incremental load and joins

Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: incremental load and joins

vinieme12 — Mon, 19 Dec 2016 14:27:05 GMT

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

topic Re: incremental load and joins in QlikView

incremental load and joins

Re: incremental load and joins