Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

guitartrova
Contributor III

Incremental load with a do while

Hello everyone, I have the following challenge I need to process the data and load it with each day and keep it in the same table, for that I am using a do while in another post I have helped do it


I thank you sincerely for your kind help


Current script

vDate = Date(Date#('20180701','YYYYMMDD'),'YYYYMMDD');    

DO WHILE vDate <= Today(1)   

  /* TRACE */ SELECT * FROM TABLE WHERE DATE = TO_DATE('$(vDate)','YYYYMMDD');    

  LET vDate = Date( vDate+1 ,'YYYYMMDD');   

LOOP

With this script it is loading the sales of 52 clients, but in reality they are approximately 10K of clients

c.pngciclo 2.png

1 Solution

Accepted Solutions
MVP
MVP

Re: Incremental load with a do while

May be try this

vDate = Date(Date#('20180701','YYYYMMDD'),'YYYYMMDD');  

DO WHILE vDate <= Today(1)  

  /* TRACE */ SELECT * FROM TABLE WHERE DATE = TO_DATE('$(vDate)','YYYYMMDD');  

  LET vDate = Date(Date#('$(vDate)', 'YYYYMMDD')+1 ,'YYYYMMDD');  

LOOP

11 Replies
MVP
MVP

Re: Incremental load with a do while

May be try this

vDate = Date(Date#('20180701','YYYYMMDD'),'YYYYMMDD');  

DO WHILE vDate <= Today(1)  

  /* TRACE */ SELECT * FROM TABLE WHERE DATE = TO_DATE('$(vDate)','YYYYMMDD');  

  LET vDate = Date(Date#('$(vDate)', 'YYYYMMDD')+1 ,'YYYYMMDD');  

LOOP

guitartrova
Contributor III

Re: Incremental load with a do while

Thank you Sunny for your help also to @petter-swho has been very helpful your help saved my day.

Happy day and thanks for everything.

listo.png

guitartrova
Contributor III

Re: Incremental load with a do while

stalwar1 I have a query regarding the data load, this day I tried to upload the data again the 10K records, but curiously I do not load the data in its entirety, you can also see that the variable 'vDate' captures the date of 28/07 / 2018 one day after having loaded the data correctly, I also see that you are only recovering 52 lines.


What would be affecting the process of loading all the data?

carga 30.png

MVP
MVP

Re: Incremental load with a do while

Did anything change between Friday and now? I mean if it worked on Friday, I am not sure why it will stop working 2-3 days later unless something changed...

guitartrova
Contributor III

Re: Incremental load with a do while

As new records are added and more than one is modified from the loaded records, I would understand that if I load the data again, the existing ones are deleted and the data is loaded from the beginning.


Will there be any way to just load the new data> = 07/28/2018 and that the data loaded on Friday remain intact in the qvf?

MVP
MVP

Re: Incremental load with a do while

In that case change your vDate to make sure it reloads from 20180728, right?

Capture.PNG

guitartrova
Contributor III

Re: Incremental load with a do while

If I change to 07/28/2018 do not lose my data uploaded until 27?


If so, will there be a way to save the last upload date and upload the new data after that date? This way you should not be changing the value manually.

MVP
MVP

Re: Incremental load with a do while

Not if this is structured as an incremental reload where the old data is stored in a qvd and new data is concatenated to it...

MVP
MVP

Re: Incremental load with a do while

This way you should not be changing the value manually.

You can look at the max date of your existing data in the qvd and only load data after the date of last pull... I mean you can do a lot of things with incremental load... (add only, add and update only, add, update and delete)... you just need to explain more clearly as to what are you trying to do?