topic Re: Incremental load by using qvd date in QlikView

Incremental load by using qvd date

khalander — Tue, 10 Jan 2017 09:14:47 GMT

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

Re: Incremental load by using qvd date

rahulpawarb — Thu, 12 Jan 2017 12:44:40 GMT

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

Re: Incremental load by using qvd date

khalander — Fri, 13 Jan 2017 04:44:52 GMT

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

Re: Incremental load by using qvd date

rahulpawarb — Fri, 13 Jan 2017 04:59:52 GMT

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

Re: Incremental load by using qvd date

khalander — Fri, 13 Jan 2017 05:50:13 GMT

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.

Re: Incremental load by using qvd date

rahulpawarb — Fri, 13 Jan 2017 06:19:08 GMT

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

Re: Incremental load by using qvd date

khalander — Fri, 13 Jan 2017 06:56:01 GMT

Hi Rahul,

It is throwing the error like field not found

PFA

Re: Incremental load by using qvd date

rahulpawarb — Fri, 13 Jan 2017 08:14:58 GMT

Hello Dada,

Could you please send the script in file?

Thank you!

Rahul

Re: Incremental load by using qvd date

khalander — Fri, 13 Jan 2017 08:30:18 GMT

Hi Rahul,

Sorry to disturb you..

PFA for script and dummy source data

Re: Incremental load by using qvd date

rahulpawarb — Fri, 13 Jan 2017 09:23:33 GMT

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

Re: Incremental load by using qvd date

khalander — Fri, 13 Jan 2017 09:44:19 GMT

Hi Rahul,

PFA for sample data.

I am using same data

Re: Incremental load by using qvd date

Vasiliy_Beshentsev — Fri, 13 Jan 2017 12:27:07 GMT

Hi, maybe this will be heplfull, read https://community.qlik.com/servlet/JiveServlet/download/650953-136192/Incremental%20Load.docx

Re: Incremental load by using qvd date

rahulpawarb — Fri, 13 Jan 2017 17:09:20 GMT

Hello Dada,

Please refer attached script as well as zip file. This will solve the purpose.

Thank you!

Rahul

Re: Incremental load by using qvd date

khalander — Mon, 16 Jan 2017 04:19:46 GMT

Hi Rahul,

Attachment is missing.. can you attach the files

Re: Incremental load by using qvd date

rahulpawarb — Mon, 16 Jan 2017 04:33:37 GMT

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

Re: Incremental load by using qvd date

khalander — Mon, 16 Jan 2017 04:55:51 GMT

Hi Rahul,

Script is perfect to my requirement. Only thing is all values are loading from sources while doing incremental load for particular month. i have inserted new record for second load instead of loading only new record but it is loading all values. But it is really helpful and perfect script..

Thanks a lot

Re: Incremental load by using qvd date

khalander — Mon, 16 Jan 2017 04:57:55 GMT

I am not able to mark it as correct answer.. Can you help me how to do that

Re: Incremental load by using qvd date

arixooo123 — Mon, 05 Jun 2017 04:04:49 GMT

Dear Rahul,

Would you please check my question and let me know your suggestion:

Incremental Loading on monthly Partitioned QVDs