
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Load New Data
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.
I will be grateful if someone attach the qvd where the above example suits
Thanks
Deepanshu
- Tags:
- deepanshu


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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);

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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).
