Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Sai33
Partner - Creator
Partner - Creator

Multiple For Each...Next in Load Script

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

Labels (1)
1 Solution

Accepted Solutions
lorenzoconforti
Specialist II
Specialist II

QlikSense example

View solution in original post

12 Replies
lorenzoconforti
Specialist II
Specialist II

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
Partner - Creator
Partner - Creator
Author

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

 

lorenzoconforti
Specialist II
Specialist II

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
Partner - Creator
Partner - Creator
Author

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

Sai33
Partner - Creator
Partner - Creator
Author

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.

 

Data1Data2YearMonthDayHour
1A2017010102
1A2017010103
1B2017010105

 

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
Partner - Creator
Partner - Creator
Author

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
Specialist II
Specialist II

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