topic Re: Incremental Load in QlikView

Incremental Load

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Can any one please help me on below requirement.

I have two source files named Original file and Change file.

Original file contains the Old Targets and Change file contains the new Targets along with Timestamp.

Daily each Numbers wise New Targets will be updated on multiple times. Out of those maximum changed date records only need to pick by each number wise like 1000, 10001, 1002 i.e, red highlighted max record for 1001

Please find the below sample source files and Qlikview app.

Please help me on writing Incremental Script.

Thanks in advance.

Re: Incremental Load

techvarun — Mon, 06 Nov 2017 11:01:30 GMT



tmp:


LOAD Year, 
     No, 
     Name, 
     ID, 
     Client, 
     Category, 
     BU, 
     Segment, 
     Type, 
     Role, 
     [New Target 1], 
     [New Target 2], 
     [New Target 3], 
     [New Target 4], 
     [Old Target 1], 
     [Old Target 2], 
     [Old Target 3], 
     [Old Target 4], 
     //[Change Date and Time], 
     TimeStamp(TimeStamp#([Change Date and Time],'DD-MM-YYYY hh:mm:ss')) as      [Change Date and Time]
FROM
[Change file.xlsx]
(ooxml, embedded labels, table is Sheet1);






filter:  
inner keep load  
    No,  
    max([Change Date and Time]) as [Change Date and Time]  
resident tmp  
group by No;  










drop table filter;

Check the above code

Re: Incremental Load

bhavvibudagam — Tue, 07 Nov 2017 08:54:55 GMT

Hi Varun,

Thank you so much for your help. I am getting max changed date records. But I need to keep the old records where changes not applied same as it is in the output.

I am adding more detail here.Please find the below screen shot.

The Original Records input file contains the Actual data. The change log file contains the changes on Original records file rows at different time stamp.

In the output I need to keep the max changed date records where changes applied and need to keep original records same as it is Based on Staff no, ID and Account Category fields .

Please help me on this logic.

Thanks in advance.

Re: Incremental Load

techvarun — Tue, 07 Nov 2017 09:29:43 GMT

Share the Original records sample which mentioned in the image in xls format as well

Re: Incremental Load

bhavvibudagam — Tue, 07 Nov 2017 09:34:34 GMT

HI Varun,

Please find the below source file attachments.

Thanks in advance.

Re: Incremental Load

techvarun — Tue, 07 Nov 2017 11:06:22 GMT

Check the attached app

Re: Incremental Load

bhavvibudagam — Tue, 07 Nov 2017 11:18:26 GMT

Thank you so much varun for your help.