topic Re: Load New Data in App Development

Load New Data

Anonymous — Sun, 03 Apr 2016 13:34:53 GMT

Hello All

Please help me in the below query. I read the qlik documents but still confused.

I have connected ODBC with Qlik Sense. In SQL the data is updated every day. As there are millions of data approx 200 million data, I need to load the new data only so that it takes less time to upload the data in qvd.

I have created a variable using LET statement which contains maximum date. I am using the variable in the where statement to get the new data.

The issue is when I load the data first time it loads the new data. But when I load it again it shows that data is loaded again which is not correct. The output must be 0 ideally

As per me the mistake is creating the variable and passing the variable. I have created a variable which picks the maximum post date from the table

I have passed this variable in the script in where clause as

Temp:

LOAD

Id,

"Date"

FROM [lib://Files/Incremental.xlsx]

(ooxml, embedded labels, table is Sheet1)

where "Date" > $(varMAXDATE);

Incta:

Load

DATE(MAX("Date")) as MAXDATE

Resident Temp;

LET varMAXDATE = PEEK('MAXDATE',0,'Incta');

I don’t have the qvd as well as script the qvd is in other environment.

Can someone please guide the best method for incremental load in qvd and correct me also.

sunindia

swuehl

maxgro‌

I will be grateful if someone attach the qvd where the above example suits

Thanks

Deepanshu

Re: Load New Data

swuehl — Sun, 03 Apr 2016 13:38:08 GMT

Try

LET varMAXDATE =Num(PEEK('MAXDATE',0,'Incta'));

and check that the variable is set correctly to a number after reload.

[I believe your formatted date won't be recognized as date after dollar sign expansion, so you could also try to embed the variable in single quotes: where "Date" > '$(varMAXDATE)'; but I would try the first suggested approach using Num() first]

Re: Load New Data

Anonymous — Sun, 03 Apr 2016 14:41:51 GMT

Thanks sir.

I used both NUM and ' ' as per your suggestion.

I have attached the snapshot numbered 1,2 & 3. With your help now I am able to show the same what problem I faces while working on client machine

I load the data once, it shows that the new data is loaded successfully (Works fine)
I loaded the data again, the show that no record is loaded (Works Fine)
I loaded the data again, it shows that all the data is loaded again ( Not working fine)

Please tell me that how numbered 3 is resolved, means even load multiple times it shows output 0 after the new data is loaded successfully

Temp:

LOAD

Id,

"Date"

FROM [lib://Files/Incremental.xlsx]

(ooxml, embedded labels, table is Sheet1)

where "Date" > '$(varMAXDATE)';

Incta:

Load

DATE(MAX("Date")) as MAXDATE

Resident Temp;

LET varMAXDATE = num(PEEK('MAXDATE',0,'Incta'));

Please guide

Thanks in advance!!

Re: Load New Data

swuehl — Sun, 03 Apr 2016 15:51:09 GMT

Well, have you checked the variable you are creating and the Incta table?

You'll see that on the second reload, the Incta table shows zero records, so not showing the max date you expect to get.

I would recommend that you retrieve your max date not from the previous load, but from looking into your historic QVD(s) you are using a base to add the new records to.

I also recommend looking into existing samples for an incremental load approach. There is a nice sample by Rob Wunderlich:

http://qlikviewcookbook.com/download/delta-load-template/

Re: Load New Data

Anonymous — Sun, 10 Apr 2016 09:10:41 GMT

Thanks sir!!

I am very new to Qlik Sense, I tried to follow the above but fail.

It would be great if you brief me the steps so that I can apply it

Deepanshu

Re: Load New Data

swuehl — Sun, 10 Apr 2016 10:42:16 GMT

Hi Deepanshu,

you have tried exactely which steps and where does this fail? Do you get a script error when executing or unexpected results?

Could you post your current script?

Re: Load New Data

Anonymous — Sun, 10 Apr 2016 11:33:55 GMT

Sir, I tried to replicate the same.

But it looks very hard to replicate, sorry for that.

Can you please tell me the other way so that the problem resolve

Reagrds

Deepanshu

Re: Load New Data

swuehl — Sun, 10 Apr 2016 11:45:08 GMT

Still not sure what exactely you have tried, but you can try with your original approach like

Temp:

LOAD

Id,

"Date"

FROM [lib://Files/Incremental.xlsx]

(ooxml, embedded labels, table is Sheet1)

where "Date" > '$(varMAXDATE)';

Incta:

Load

DATE(MAX("Date")) as MAXDATE

Resident Temp;

LET varMAXDATE = RangeMax( num(PEEK('MAXDATE',0,'Incta')), varMAXDATE);

Re: Load New Data

Anonymous — Sun, 10 Apr 2016 12:25:49 GMT

Thanks a lot sir for the help!!

I tried it but first time when I load the data it fetch only new data.

Second time when I load the data it pulls no record(ideally it is correct),

But in visualization due to 0 data load all the fields are empty. Somehow all the data becomes 0

Do we need to do concatenate it with actual one

Thanks in advance!!

Regards

Deepanshu

Re: Load New Data

swuehl — Sun, 10 Apr 2016 12:39:03 GMT

sure, if you only load new data and there aren't any, no data will be shown.

I understood your script is just pulling in new data (see also your title), then stores the new data to QVD.

You would then concatenate the new data with the historic data already loaded to get the complete data set.

Here is another try for an explanation of the principle:

Loading new and updated records with incremental load ‒ Qlik Sense‌

With the script I've posted above, you need to reset the variable (to zero) if you want bring in all records (first time load).