Multiple For Each...Next in Load Script

[Sai33]

Hello All,

I'm looking to load my Data from a Datalake to Qlik with multiple For each....next loops.

For Each Year in ($Year)

      -  ($Year has a list of all the Years 2019,2018)

For Each Month in ($Month)

      - ($Month has a list of all the Months 1,2,3,4,5,6...)

For Each Date in ($Date)

      - ($Date has a list of all Dates 1,2,..31)

For Each Hour in ($Hour)

     - ($Hour has list of all Hours 1,2,..24)

[Data]:

LOAD

 *

from Table

Where Year=($Year) and Month=($Month) and Day=($Day) and Hour=($Hour);

STORE * from [Data] into ($Year)($Month) ($Day)($Hour)_Data.qvd;

Drop Table Data;

Next Year

Next Month

Next Day

Next Hour

Exit Script;

This Script doesn't work as expected with multiple For Each Next conditions. Can any one please suggest how to handle such cases involving multiple loops.

TIA!!

[lorenzoconforti]

Does the script work with just one for each? i.e. if you stop at the year level

Also, when you create your Data table it looks like it might auto-concatenate with Table and when you drop it at the first iteration it might drop the whole Table

Does it save the first file? What error does it give you?

[Sai33]

It does work with only one Next. But, after saving the first qvd it ends with an Error saying 'Error at Next ...'

[lorenzoconforti]

Probably because it's concatenating the tables

Tray changing your data table and put NoConcatenate before load

Data:

NoConcatenate Load

[Kushal_Chawda]

Can you please explain the actual requirement. It will help to provide better solution instead of using complex for loops

[Sai33]

NoConcatenate has also not helped me solve the issue. Thanks for the suggestion though!

[Sai33]

So, basically i have my data in a Datalake with year month day and hour as base fields for Delta Load.

Here, i'm try to build a For Each...Next loop by passing the Next year month day and hour as Variables in the Where Condition to the Load Statement and then save the extracted data as a QVD along with the year month day and hour field in the QVD name.

Data1	Data2	Year	Month	Day	Hour
1	A	2017	01	01	02
1	A	2017	01	01	03
1	B	2017	01	01	05

From the above Table i would like to generate 3 QVD files:

2017010102_Data.qvd

2017010103_Data.qvd

2017010105_Data.qvd

Each time the loop has the execute the first Where condition(Here, Year=2017 and Month=01 and Day=01 and Hour=02) save it as a qvd file and then execute the second qvd(Year=2017 and Month=01 and Day=01 and Hour=03) and save it as second qvd.

I hope this explains my problem lot better. Please, do let me know if you need further info. 

Thanks for your help.

[Kushal_Chawda]

Is there a specific need to create QVDs like this. Let's say in your table you have million records for multiple year,day,month combination then you will end up creating so many QVDs which is not a good practice as it will be difficult to maintain as well as design wise make the script more complex.

[Sai33]

Hi Kush,

Yes, as you mentioned each partition(combination of year month day and hour) has more than 1M records.

Here, we are trying to load more than 301 M records into Qlik and then this will keep increasing in almost the same quantity every hour.

So, we don't want to load all the records into Qlik each time rather this is the best approach we figured.

[lorenzoconforti]

Hi,

I think there might be a couple of issues with your syntax. I've recreated an app which is working for me that shows how to implement this

Instead of storing the values you are looping through in a variable I've created 3 different tables

Make sure you create a subfolder QVD where the script saves the files

Also, you are posting in the Qlik Sense forum but the store command you use uses QlikView syntax so not sure which application you are using. I'll post two separate examples, one for QlikView and one for Qlik Sense

Lorenzo