Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MarioCenteno
Creator III
Creator 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
sunny_talwar

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

View solution in original post

11 Replies
sunny_talwar

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

MarioCenteno
Creator III
Creator III
Author

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

MarioCenteno
Creator III
Creator III
Author

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

sunny_talwar

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...

MarioCenteno
Creator III
Creator III
Author

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?

sunny_talwar

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

Capture.PNG

MarioCenteno
Creator III
Creator III
Author

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.

sunny_talwar

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...

sunny_talwar

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?