Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
QlikSense example
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?
It does work with only one Next. But, after saving the first qvd it ends with an Error saying 'Error at Next ...'
Probably because it's concatenating the tables
Tray changing your data table and put NoConcatenate before load
Data:
NoConcatenate Load
Can you please explain the actual requirement. It will help to provide better solution instead of using complex for loops
NoConcatenate has also not helped me solve the issue. Thanks for the suggestion though!
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.
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.
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.
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