topic Re: Create the snapshot date for incremented data's in QlikView

Create the snapshot date for incremented data's

Anonymous — Mon, 08 May 2017 22:10:36 GMT

Hi All,

I have initially generate the snapshot date for my source file based upon the start and end date of the jobs.

Actual Source:(Source.qvd)

Job Id	Start date	End date
J1	5/1/2017	12/31/9999
J2	5/2/2017	12/31/9999
J3	5/2/2017	12/31/9999

Snapshot date(today()-1 scenario) generated based upon the above source at initially like as below and store in (Test1.qvd)

Job Id	Snap_Date
J1	5/1/2017
J1	5/2/2017
J1	5/3/2017
J1	5/4/2017
J1	5/5/2017
J1	5/6/2017
J1	5/7/2017
J2	5/1/2017
J2	5/2/2017
J2	5/3/2017
J2	5/4/2017
J2	5/5/2017
J2	5/6/2017
J2	5/7/2017
J3	5/1/2017
J3	5/2/2017
J3	5/3/2017
J3	5/4/2017
J3	5/5/2017
J3	5/6/2017
J3	5/7/2017

After that I was performing the I will perform the incremental load to get the update records from Source.qvd like as,

Job Id	Start date	End date
J1	5/1/2017	12/31/9999
J2	5/2/2017	12/31/9999
J3	5/2/2017	12/31/9999
J4	5/7/2017	12/31/9999

From the above source I need to generate the snapshot date for J1,J2,J3,J4 like as below table.

Job Id	Snap_Date
J1	5/8/2017
J2	5/8/2017
J3	5/8/2017
J4	5/7/2017
J4	5/8/2017

Can anyone give me suggestions to get it done

Re: Create the snapshot date for incremented data's

Anil_Babu_Samineni — Tue, 09 May 2017 00:41:12 GMT

You want to show Table like last one. But you mentioned Today()-1 and here, you may need Today()+1 for Max date.

Re: Create the snapshot date for incremented data's

Anonymous — Tue, 09 May 2017 00:44:41 GMT

Simply i put today () -1 scenario for reference. Daily basis, it need to get generate the snapshot date.

Re: Create the snapshot date for incremented data's

Anil_Babu_Samineni — Tue, 09 May 2017 01:20:44 GMT

What is your format?

May be this

Source:

LOAD * INLINE

[

Job Id	Start date	End date
J1	5/1/2017	12/31/9999
J2	5/2/2017	12/31/9999
J3	5/2/2017	12/31/9999

];

NoConcatenate

Final:

LOAD

[Job Id],

Date([Start date],IterNo()-1) as [Start date],

MakeDate(7,5,2017) as [To date]

Resident Source

While Date( [Start date]+IterNo()-1) <= [To date];

Drop Table Source;

Re: Create the snapshot date for incremented data's

Digvijay_Singh — Tue, 09 May 2017 04:09:58 GMT

This should help you out -

Source:

Load * inline [

Job Id, Start date, End date

J1, 5/1/2017, 12/31/9999

J2, 5/2/2017, 12/31/9999

J3, 5/2/2017, 12/31/9999

J4, 5/7/2017, 12/31/9999 ];

if isnull(filetime('Test1.qvd')) then //First time QVD is loaded with this script code;

Test1:

Load [Job Id], Date([Start date] + iterno() -1) as Snap_Date

resident Source

While [Start date] + iterno() -1 <= today(1)-1;

Store Test1 into Test1.qvd(qvd);

drop table Source;

EXIT SCRIPT;

end if

Test1: //If QVD already exists, then load old records, we are also creating new temp Job ID field to compare later.

LOAD [Job Id],

[Job Id] as Temp_Job_Id,

Snap_Date

FROM

Test1.qvd

(qvd);

Temp2: //We want to load new records for existing Job IDs from the last max date, don't need to repeat the logic from beginning.

Load [Job Id],

max(Snap_Date) as Max_Date

Resident Test1

Group By [Job Id];

Concatenate(Test1) //This will load existing job Ids with new snap date from last loaded date to today date.

Load [Job Id],

Date(Max_Date + IterNo() ) as Snap_Date

Resident Temp2

While Max_Date + IterNo() <= today(1)-1;

NoConcatenate

Temp1: //This will get newly added job Ids in the source file. This logic can be even added before above mentioned logic of identifying max dates.

Load *

Resident Source

Where not exists(Temp_Job_Id,[Job Id]);

Concatenate(Test1) //This will create snap dates for newly added job ids.

Load [Job Id],

Date([Start date] + IterNo() -1) as Snap_Date

Resident Temp1

While [Start date] + IterNo() -1 <= today(1);

;

Store Test1 into Test1.qvd(qvd);

drop table Source,Temp1,Temp2;

drop field Temp_Job_Id;