Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Peony
Creator III
Creator III

Split file load on parts by date

Hi all. 

I have file that should be loaded by parts based on dates in it.
Lets say I have file with some dates data in it  like below.

Peony_0-1691585809208.png

And I need to load this file by parts for each 4 days starting from the oldest date. Like first  iteration should be  20/07/2023  - 23/07/2023, second - 24/07/2023  - 27/07/2023, third - 28/07/2023  - 30/07/2023.

How to make such split correctly? Or is it possible to do at all?

Labels (3)
1 Solution

Accepted Solutions
marcus_sommer

You may take a similar approach as starting point to create the needed loop-information, for example by:

DateList:
load date(startdate + recno() - 1) as Date, ceil(recno() / 4) as DateGroup
autogenerate enddate - startdate;

DateGroup:
load DateGroup, concat(Date, ',') as DateString
resident DateList group by DateGroup;

and then using fieldvaluelist() and/or fieldvalue() or peek() to loop through these tables and picking the needed Date or DateString or similar created information into a variable to prepare an appropriate where-clause or adjusting the file-name with it.

View solution in original post

4 Replies
marcus_sommer

It's not quite clear what do you mean with split the files but the following would provide a key-value with an appropriate grouping:

load *, ceil(rowno() / 4) as DateGroup
resident Source order by date_time desc;

Peony
Creator III
Creator III
Author

Thank you for you reply @marcus_sommer . Unfortunately this idea does not works for me.  I have large xml file in some web site. And once I need to load it. I can't do this because time of connection with this source is limited and  I just can't load it all at once .
So I decided try to create some loop where I may set date period and load part of xml data during connection is open. Than reconnect , take another part and concatenate it with previous loaded data. And repeat this loop until I will get all xml file. 

marcus_sommer

You may take a similar approach as starting point to create the needed loop-information, for example by:

DateList:
load date(startdate + recno() - 1) as Date, ceil(recno() / 4) as DateGroup
autogenerate enddate - startdate;

DateGroup:
load DateGroup, concat(Date, ',') as DateString
resident DateList group by DateGroup;

and then using fieldvaluelist() and/or fieldvalue() or peek() to loop through these tables and picking the needed Date or DateString or similar created information into a variable to prepare an appropriate where-clause or adjusting the file-name with it.

Peony
Creator III
Creator III
Author

Thanks @marcus_sommer ! Will try to implement this example into my solution.