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: 
khalander
Creator II
Creator II

Incremental load by using qvd date

Hi Everyone,

I have one requirement like system has to do incremental process based on date in particular qvd

For Example. I have source file like

IDNameDate
1A01/01/2016
2B31/01/2016
3C01/02/2016
4D24/02/2016
5E01/03/2016
6F15/03/2016

Requirements:

1) Store the month wise data into particular month qvd like jan data into X_012016.qvd, Feb data into X_022016 and so on.

2) Now i need to write one for loop like system has to check the dates in oldest qvd first( in our case Jan month qvd) and then it should do incremental process( Insert or update) from DB and jan qvd should contains updated jan data. and it has to check for all qvd and hs to update data for all month qvds.

Kindly do the needful

1 Solution

Accepted Solutions
rahulpawarb
Specialist III
Specialist III

Hello Dada,

I could see the attachments. However, I have re-attached it herewith.

Also, refer below given modified script:

DateListTemp:

LOAD

  Distinct LEFT(RIGHT(Date([Transaction date],'DD/MM/YYYY'),7),2)& RIGHT(Date([Transaction date],'DD/MM/YYYY'),4) AS MonthYear

FROM [Copy of incree load data.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

DateList:

LOAD

  MonthYear

Resident DateListTemp

ORDER BY 1;

Drop Table DateListTemp;

//EXIT SCRIPT;

FOR i=0 TO NoOfRows('DateList')-1

  LET vMonthYear = PEEK('MonthYear', $(i), 'DateList');

  LET vQVDPath = 'QVDs\'; // Set QVD Storage Directory

  Sales:

  LOAD

    Region,

    id,

    product,

    sales,

    [Transaction date]

  FROM [Copy of incree load data.xlsx]

  (ooxml, embedded labels, table is Sheet1)

  Where LEFT(RIGHT(Date([Transaction date],'DD/MM/YYYY'),7),2)& RIGHT(Date([Transaction date],'DD/MM/YYYY'),4) = $(vMonthYear);

  // Check to see if this is the first reload. if it is, skip this step

  if not isnull(QVDCreateTime('$(vQVDPath)Sales_$(vMonthYear).qvd')) THEN

    Concatenate(Sales)

    LOAD *

    FROM $(vQVDPath)Sales_$(vMonthYear).qvd(qvd)

    WHERE Not Exists(id);

  end if

  // If data exists within table, store to QVD.

  if NoOfRows('Sales') > 0 THEN

    STORE Sales INTO $(vQVDPath)Sales_$(vMonthYear).qvd;

    Drop Table Sales;

  end if

Next

Drop Table DateList;

Thank you!

Rahul

View solution in original post

17 Replies
rahulpawarb
Specialist III
Specialist III

Hello Dada,

Hope you are well there.

Please refer below given sample script to solve your concern (you need to alter it as per your need).

DateList:

SQL SELECT DISTINCT CONCAT(LEFT(RIGHT(Date,7),2), RIGHT(Date,4)) AS MonthYear

FROM <tablename>

ORDER BY CONCAT(LEFT(RIGHT(Date,7),2), RIGHT(Date,4));

FOR i=0 TO NoOfRows('DateList')-1

LET vMonthYear = PEEK('MonthYear', $(i), 'DateList');

LET vQVDPath = 'Qvd\'; // Set QVD Storage Directory

LET vExecTime = UTC();

SET vLastExecTime = 0; // Resetting vLastExecTime

// As long as a QVD already exists, find the latest timestamp for modified records.

//This will be used to generate the delta set.

if not isnull(QVDCreateTime('$(vQVDPath)X_$(vMonthYear).qvd'))

then

LoadTime:

Load Max(LastModifiledDate) as LastModifiedDate

From $(vQVDPath)X_$(vMonthYear).qvd(qvd);

LET vLastExecTime = Peek('LastModifiedDate',0,'LoadTime');

Drop Table LoadTime;

end if

<tablename>:

SQL SELECT Id,

...

FROM <tablename>

WHERE LastModifiedDate >= $(vLastExecTime)

AND LastModifiedDate <= $(vExecTime);

// Check to see if this is the first reload. if it is, skip this step

if not isnull(QVDCreateTime('$(vQVDPath)X_$(vMonthYear).qvd'))

then

Concatenate(<tablename>)

LOAD *

FROM $(vQVDPath)X_$(vMonthYear).qvd(qvd)

WHERE Not Exists(Id);

end if

// If data exists within table, store to QVD.

if NoOfRows('<tablename>') > 0

then

STORE <tablename> INTO $(vQVDPath)X_$(vMonthYear).qvd;

Drop Table <tablename>;

end if

Next

Drop Table DateList;

Thank you!

Rahul

khalander
Creator II
Creator II
Author

Hi Rahul,

I am good.Thank you... Hope you are well

Thanks for the reply..

I think this script will work but it is throwing error for 'DISTINCT CONCAT(LEFT(RIGHT(Date,7),2), RIGHT(Date,4)) AS MonthYear' this condition

I am loading data from excel sheet.. i think order by function desnt work.

Could you please do the needful.

Regards,

Dada Khalander

rahulpawarb
Specialist III
Specialist III

Hello Dada,

Thank you for asking.

I have wrote the load load statement by considering RDBMS table (Concat syntax may vary from one RDBMS to another.Also Order By...). However, you can use LEFT(RIGHT("sales date",7),2) & RIGHT("sales date",4) AS MonthYear in your script and try... To Order the set you you can use resident load and do the ordering of MonthYear field (Just a thought).

Hope this will help.

Regards!

Rahul

khalander
Creator II
Creator II
Author

Hi Rahul,

Qvd's creating month wise but entire data is storing in all QVD's instead of storing month wise data in particular month.

Data should store in qvd like jan month data in X_201601.qvd.

rahulpawarb
Specialist III
Specialist III

Hello Dada,

Please change the filter condition as below:

Original:

<tablename>:

SQL SELECT Id,

...

FROM <tablename>

WHERE LastModifiedDate >= $(vLastExecTime)

AND LastModifiedDate <= $(vExecTime);

Modified:

<tablename>:

SQL SELECT Id,

...

FROM <tablename>

WHERE LEFT(RIGHT(LastModifiedDate,7),2) & RIGHT(LastModifiedDate,4) = $(vMonthYear);


I hope this will help you.

Thank you!

Rahul

khalander
Creator II
Creator II
Author

Hi Rahul,

It is throwing the error like field not found

PFA

rahulpawarb
Specialist III
Specialist III

Hello Dada,

Could you please send the script in file?

Thank you!

Rahul

khalander
Creator II
Creator II
Author

Hi Rahul,

Sorry to disturb you..

PFA for script and dummy source data

rahulpawarb
Specialist III
Specialist III

Hello Dada,

To further analyze, could you please share the sample application along with sample source data file, If possible.

This will help me to provide you error free solution.

Thank you!

Rahul