Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

incremental load and joins

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,

1 Reply
vinieme12
Champion III
Champion III

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 (qvd);

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";

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.