Solved: Incremental load by using qvd date - Qlik Community

khalander · ‎2017-01-10

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

ID	Name	Date
1	A	01/01/2016
2	B	31/01/2016
3	C	01/02/2016
4	D	24/02/2016
5	E	01/03/2016
6	F	15/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

rahulpawarb · ‎2017-01-15

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

rahulpawarb · ‎2017-01-12

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 · ‎2017-01-12

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 · ‎2017-01-12

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 · ‎2017-01-13

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 · ‎2017-01-13

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 · ‎2017-01-13

Hi Rahul,

It is throwing the error like field not found

PFA

rahulpawarb · ‎2017-01-13

Hello Dada,

Could you please send the script in file?

Thank you!

Rahul

khalander · ‎2017-01-13

Hi Rahul,

Sorry to disturb you..

PFA for script and dummy source data

rahulpawarb · ‎2017-01-13

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