Qlik Automate (自動化)によるQlik SenseとHubSpotの連携④

[
{“properties”:{
    “email”:”test1@mail”,
    “firstname”:”test1姓”,
    “lastname”:”test1名”
}},
{“properties”:{
    “email”:”test2@mail”,
    “firstname”:”test2姓”,
    “lastname”:”test2名”
}}
]

// プロパティの項目名とHubSpotの内部名のMappingテーブルを作成
Map_Hubspot_Contact_Property:
Mapping
Load * INLINE [
項目名,内部名
Eメール,email
姓,firstname
名,lastname
];

// 顧客データをロード
ContactCreate_before_crosstable:
LOAD
	RowNo() as row_no,
    Eメール,
    姓,
    名
FROM [顧客データ]

// 100件ずつのリストを作成
ContactCreate_cross:
CrossTable('key','value',1)
Load * Resident ContactCreate_before_crosstable;

Drop Table ContactCreate_before_crosstable;

ContactCreate_concat:
Load
	row_no,
    '"' & ApplyMap('Map_Hubspot_Contact_Property',key) & '"' & ':' & '"' & value & '"' as json_field
Resident ContactCreate_cross;

Drop Table ContactCreate_cross;

NoConcatenate
ContactCreate_concat2:
Load
	row_no,
    '{' & Concat(DISTINCT json_field,',') & '}' as json_field
Resident ContactCreate_concat
Group by row_no;

Drop Table ContactCreate_concat;

Let vRow_num_all_contactcreate = NoOfRows('ContactCreate_concat2');
Let vRow_num_split_contactcreate = Floor($(vRow_num_all_contactcreate)/100);

For i = 0 to $(vRow_num_split_contactcreate)
	ContactCreate_concat3:
    Load
    	row_no,
        JsonSet('{}','/properties',json_field) as json_field2
    Resident ContactCreate_concat2
    Where row_no > $(i)*100 and row_no <=($(i)+1)*100;
    
    Contact_create_json:
    Load
    	'[' & Concat(json_field2,',') & ']' as json_Contact_create
    Resident ContactCreate_concat3;
    
    Drop Table ContactCreate_concat3;
Next i

Drop Table ContactCreate_concat2;