Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I am trying to implement an incremental load from existing qvd file, And append new data to a new QVD file.
After appending with new raw data and save in new qvd, I will need to perform two left join:
1. With a table that contains 20 million records, And the information in the table is Permanent
Example of Permanent columns: Last Name, Date of birth.
2. With a table that contains 20 million records, And the information in the table is Variable
Example of columns: Status.
To deal with the first left join I used multiple mapping with the Fact table (i need to implement the mapping only on the new data).
(Miguel Roone example. (Mapping Multiple Fieds with only one Mapping Load ))
the second left join will be implement on all the data.
the Performance is slow and I'm not sure this is the right way to build such a script Sample script attached.
Sample script attached.
Thanks in advance,
try the below once
ApplicantCustomer:
mapping LOAD Leads_Unique_Num,
ApplicantCustomerData
FROM
(qvd);
LET Is_QVD_File_Exists = ISNULL(QVDCREATETIME('E:\QLIKVIEW\Data\QVD2.qvd'));
IF $(Is_QVD_File_Exists) = -1 THEN
Fact_table:
LOAD *,
SubField(ALL_DATA,'|',1) as age,
SubField(ALL_DATA,'|',2) as [Marital Status],
SubField(ALL_DATA,'|',3) as [Num Of Children],
SubField(ALL_DATA,'|',4) as [Education],
SubField(ALL_DATA,'|',5) as [Occupation],
SubField(ALL_DATA,'|',6) as [First Name],
SubField(ALL_DATA,'|',7) as [Last Name],
SubField(ALL_DATA,'|',8) as [Email],
SubField(ALL_DATA,'|',9) as [Gender],
SubField(ALL_DATA,'|',10) as [Birth Date],
SubField(ALL_DATA,'|',11) as [City],
SubField(ALL_DATA,'|',12) as [Street],
SubField(ALL_DATA,'|',13) as [Residence Country],
SubField(ALL_DATA,'|',14) as [Paid as LeadsData]
;
load *,
ApplyMap('ApplicantCustomer',Leads_Unique_Num) as ALL_DATA
;
LOAD Date_Day,
Date_Hour,
Unique_Num,
ID,
Department,
UpType,
Basket_OrderId,
If( match(ActionType_ID,5,6,7,8)>0 and Department = 2,ApplyMap('Leads_Mapping',Unique_Num),Unique_Num) as Leads_Unique_Num,
If( match(ActionType_ID,5,6,7,8)>0 and Department = 2,ApplyMap('Leads_Mapping',Unique_Num),Unique_Num) as Unique_Num_Shop_To_Usafis,
from
DROP FIELD ALL_DATA FROM Fact_table;
STORE Fact_table into "E:\QLIKVIEW\Data\QVD2.qvd";
else
Fact_table:
LOAD * FROM "E:\QLIKVIEW\Data\QVD2.qvd" (qvd);
NoConcatenate
Fact_table_temp:
LOAD * FROM "
SubField(ALL_DATA,'|',3) as [Num Of Children],
SubField(ALL_DATA,'|',4) as [Education],
SubField(ALL_DATA,'|',5) as [Occupation],
SubField(ALL_DATA,'|',6) as [First Name],
SubField(ALL_DATA,'|',7) as [Last Name],
SubField(ALL_DATA,'|',8) as [Email],
SubField(ALL_DATA,'|',9) as [Gender],
SubField(ALL_DATA,'|',10) as [Birth Date],
SubField(ALL_DATA,'|',11) as [City],
SubField(ALL_DATA,'|',12) as [Street],
SubField(ALL_DATA,'|',13) as [Residence Country],
SubField(ALL_DATA,'|',14) as [Paid as LeadsData]
;
load *,
ApplyMap('ApplicantCustomer',Leads_Unique_Num) as ALL_DATA
;
LOAD Date_Day,
Date_Hour,
Unique_Num,
ID,
Department,
UpType,
Basket_OrderId,
If( match(ActionType_ID,5,6,7,8)>0 and Department = 2,ApplyMap('Leads_Mapping',Unique_Num),Unique_Num) as Leads_Unique_Num,
If( match(ActionType_ID,5,6,7,8)>0 and Department = 2,ApplyMap('Leads_Mapping',Unique_Num),Unique_Num) as Unique_Num_Shop_To_Usafis
Resident Fact_table_temp;
left join(Fact_table)
Leads:
LOAD Leads_Unique_Num,
Leads_Status,
Lead_Category,
Lead_Center,
Lead_MainPhone,
Lead_MobilePhone,
Pilot
FROM
(qvd);
DROP FIELD ALL_DATA FROM Fact_table;
drop Table Fact_table_temp;
store Fact_table into "E:\QLIKVIEW\Data\QVD2.qvd";