Create the snapshot date for incremented data's

[Anonymous]

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

[Anil_Babu_Samineni]

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

[Anonymous]

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

[Anil_Babu_Samineni]

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;

[Digvijay_Singh]

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;