Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
deepanshu_chamo1
Contributor
Contributor

Data load

Hello All,

Please help me in the incremental data load.


I have declared the variable which pulls the max. date, then used this variable in main script.


The issue I am facing is that when I first upload the data it loads the new data correctly

When I reload the data it shows that no data is loaded i.e. 0 lines fetched but it removes the old data.

When I reload the data again it pulls all the data from the starting

I want that it pulls the new data only. When I reload the data it pulls zero lines with old data in it and this process repeats again and again

LOAD SCRIPT:


LIB CONNECT TO 'TIGUNVMBGWL0011';

LOAD CustomerName,

    ContactName,

    POSTDATE;

SQL SELECT CustomerName,

    ContactName,

    POSTDATE

FROM msdb.dbo.Customers

Where POSTDATE >'$(Last_Update_Date)';

VARIABLE:


Last_Update:

LOAD Max(POSTDATE) as maxdate

resident Customers;

Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'YYYY-MM-DD');

Please find the snapshot of the load

  1. When I load the data first time it loads the new data only
  2. When I reload the data it show 0 lines fetched
  3. When I reload the data it loads all the data

Please help an guide me where I correct myself

reddys310

New to Qlik Sense

1 Solution

Accepted Solutions
oscarmqz
Creator III
Creator III

Hi,

The "error" in the concatenate is ok, QlikView has some bugs like this, there's no error but still you'll see it. To remove the sintetic key add this right after the ENDIF

DROP TABLE Last_Update;

So basically I'm doing this:

  1. Check if there's a QVD already
    1. If yes, then
      1. Load the current content from it
      2. Append new data from database
      3. Store all the dataset in the QVD (this new version overwrites the old one)
    2. If no, then
      1. Read the data directly from database
      2. Store the data in new QVD to be used next time you run it

By the way I forgot to add a variable value in case the QVD does not exist... so the whole script would be this

LIB CONNECT TO 'TIGUNVMBGWL0011';

SET QVD_Path  = 'LIB://Qlik_QVD/SALES.QVD';

LET vQVDExists = NOT isnull(QVDCreateTime('$(QVD_Path)'));

Let Last_Update_Date = '2000/01/01'; //Use whatever date you want here to pull all your data from database

IF $(vQVDExists) THEN

    SALES:

    LOAD *

    FROM   

    [$(QVD_Path)]

    (qvd);

   

    Last_Update:

    LOAD Max(POSTDATE) as maxdate

    resident SALES;

    Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'YYYY-MM-DD');

   

    Set vConcatenate = Concatenate;

ENDIF

DROP TABLE Last_Update; //getting rid of temporal table created to pull max date from QVD to avoid syntetic keys

$(vConcatenate)

LOAD  ID,

    CustomerName,

    ContactName,

    POSTDATE;

SQL SELECT  ID,

    CustomerName,

    ContactName,

    POSTDATE

FROM msdb.dbo.Customers

Where POSTDATE >'$(Last_Update_Date)';

STORE SALES INTO $(QVD_Path) (QVD);

View solution in original post

11 Replies
oscarmqz
Creator III
Creator III

Can you post the whole script?

I believe you are not saving the data into a QVD file, you cannot skip this when using incremental loads.

Regards

deepanshu_chamo1
Contributor
Contributor
Author

Thanks for your reply sir!!

The above mentioned is my whole script.. Do I miss something please let me know..

This is just a dummy data. I need to apply this in my whole data set in which everyday lakhs of rows are added.

Yep. I am not saving the data in QVD file.

If I understand correctly from your second line I have no option to skip this. But this will create a problem for me if the data loads twice because it removes all the data second time

Please suggest

Thanks

oscarmqz
Creator III
Creator III

Hi,

Please see this webpage. You are missing a couple things to make an incremental load work. You need to read a QVD with previous data and append new data to it, it cannot be done on the fly from a database.

https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

Regards,

Oscar

deepanshu_chamo1
Contributor
Contributor
Author

Thanks a lot sir. It really helps!!

But i have few doubts. It would be great if you resolve my doubts.

i have created a qvd which pulls all data in it. I have declared a variable by which I am able to know my last date.

I use this qvd for incremental load by following the above link steps ( Insert, Update & Delete).

Queries:

1. Do I need to declare a variable in my incremental load QVD, if yes how and if not how it reads the variable which pulls the latest date

2. In the link, we have inner join with the ID, what will happen if we do not have any unique id or if we have multiple id

3. I am missing something because still using the variable it is loading full data set, not those with the latest date data. Might be the variable is not working fine as I have not declared the variable in Incremental load

QVD WHICH PULLS ALL DATA AND LAST DATE:

LIB CONNECT TO 'TIGUNVMBGWL0011';

SALES:

LOAD CustomerName,

    ContactName,

    POSTDATE;

SQL SELECT CustomerName,

    ContactName,

    POSTDATE

FROM msdb.dbo.Customers;

Last_Update:

LOAD Max(POSTDATE) as maxdate

resident SALES;

Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'YYYY-MM-DD');

INCREMENTAL LOAD QVD:

LIB CONNECT TO 'TIGUNVMBGWL0011';

Incrementals:

LOAD

  ID,

    CustomerName,

    ContactName,

    POSTDATE;

SQL SELECT

  ID,

  CustomerName,

    ContactName,

    POSTDATE

FROM msdb.dbo.Customers

Where POSTDATE >'$(Last_Update_Date)';

CONCATENATE

LOAD

*

FROM 'LIB://Qlik_QVD/SALES.qvd'(qvd);

INNER JOIN

LOAD

  ID;

SQL SELECT

  ID

FROM msdb.dbo.Customers

Please guide me.

oscarmqz
Creator III
Creator III

How are you creating your QVD? Can you share your script?

deepanshu_chamo1
Contributor
Contributor
Author

Hello Sir

QVD Creation Script:

LIB CONNECT TO 'TIGUNVMBGWL0011';

SALES:

LOAD CustomerName,

    ContactName,

    POSTDATE;

SQL SELECT CustomerName,

    ContactName,

    POSTDATE

FROM msdb.dbo.Customers;

Last_Update:

LOAD Max(POSTDATE) as maxdate

resident SALES;

Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'YYYY-MM-DD');

STORE SALES

INTO 'LIB://Qlik_QVD/SALES.QVD';

INCREMENTAL LOAD SCRIPT:

LIB CONNECT TO 'TIGUNVMBGWL0011';

Incrementals:

LOAD

  ID,

    CustomerName,

    ContactName,

    POSTDATE;

SQL SELECT

  ID,

  CustomerName,

    ContactName,

    POSTDATE

FROM msdb.dbo.Customers

Where POSTDATE >'$(Last_Update_Date)';

CONCATENATE

LOAD

*

FROM 'LIB://Qlik_QVD/SALES.qvd'(qvd);

STORE Incrementals

INTO 'LIB://Qlik_QVD/Incrementals.QVD';

Attached is the qvd files

Please let me know where is the issue

Thanks

oscarmqz
Creator III
Creator III

The incremental load should happen in the same script (you can split it in two different tabs, but not different scripts). Here's what I believe you need.... let me know if this works. Regards

LIB CONNECT TO 'TIGUNVMBGWL0011';

SET QVD_Path  = 'LIB://Qlik_QVD/SALES.QVD';

LET vQVDExists = NOT isnull(QVDCreateTime('$(QVD_Path)'));

IF $(vQVDExists) THEN

    SALES:

    LOAD *

    FROM   

    [$(QVD_Path)]

    (qvd);

   

    Last_Update:

    LOAD Max(POSTDATE) as maxdate

    resident SALES;

    Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'YYYY-MM-DD');

   

    Set vConcatenate = Concatenate;

ENDIF

$(vConcatenate)

LOAD  ID,

    CustomerName,

    ContactName,

    POSTDATE;

SQL SELECT  ID,

    CustomerName,

    ContactName,

    POSTDATE

FROM msdb.dbo.Customers

Where POSTDATE >'$(Last_Update_Date)';

STORE SALES INTO $(QVD_Path) (QVD);

deepanshu_chamo1
Contributor
Contributor
Author

Thanks a lot sir for the help!!

Thanks a lot sir for the help!!

Yes, this is same thing I want.

But in loading we have the Synthetic Key, how we remove that key. There is a table "Last_Update" I am not able to find out where this table is created.

It also shows an error while doing CONCATENATE

Below is the screenshot of it:Issue.pngSyn Key.png

It would be grateful for me if you can explain me what you did in the script part briefly and also help me to remove the synthetic key.

Thanks a lot for help!!

Please guide me

Thanks!!

oscarmqz
Creator III
Creator III

Hi,

The "error" in the concatenate is ok, QlikView has some bugs like this, there's no error but still you'll see it. To remove the sintetic key add this right after the ENDIF

DROP TABLE Last_Update;

So basically I'm doing this:

  1. Check if there's a QVD already
    1. If yes, then
      1. Load the current content from it
      2. Append new data from database
      3. Store all the dataset in the QVD (this new version overwrites the old one)
    2. If no, then
      1. Read the data directly from database
      2. Store the data in new QVD to be used next time you run it

By the way I forgot to add a variable value in case the QVD does not exist... so the whole script would be this

LIB CONNECT TO 'TIGUNVMBGWL0011';

SET QVD_Path  = 'LIB://Qlik_QVD/SALES.QVD';

LET vQVDExists = NOT isnull(QVDCreateTime('$(QVD_Path)'));

Let Last_Update_Date = '2000/01/01'; //Use whatever date you want here to pull all your data from database

IF $(vQVDExists) THEN

    SALES:

    LOAD *

    FROM   

    [$(QVD_Path)]

    (qvd);

   

    Last_Update:

    LOAD Max(POSTDATE) as maxdate

    resident SALES;

    Let Last_Update_Date = Date(peek('maxdate',0,'Last_Update'),'YYYY-MM-DD');

   

    Set vConcatenate = Concatenate;

ENDIF

DROP TABLE Last_Update; //getting rid of temporal table created to pull max date from QVD to avoid syntetic keys

$(vConcatenate)

LOAD  ID,

    CustomerName,

    ContactName,

    POSTDATE;

SQL SELECT  ID,

    CustomerName,

    ContactName,

    POSTDATE

FROM msdb.dbo.Customers

Where POSTDATE >'$(Last_Update_Date)';

STORE SALES INTO $(QVD_Path) (QVD);

View solution in original post