Incremental load with a do while

[MarioCenteno]

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

[sunny_talwar · Accepted solution]

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]

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.

[MarioCenteno]

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?

[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]

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?

[MarioCenteno]

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?