Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 4 excel files which loaded as below:
I created 2 new columns by scripting as shown below:
However if I loaded the excel file without following the months, the result will be different.
For example: I load Feb file then only Jan file: the outcome will be like below:
Is there any scripting could fix this matter.
I would like the script could define the order no. base on month instead of which files load in first.
Hi Chung,
Use Below script to load your files in specific order.
FileName_Temp:
Load
Distinct
Subfield(FileName(),'.',1) as Month,
Date(Date#(Subfield(FileName(),'.',1),'MMM'),'MM') as MonthNo
FROM [lib://Data/*.xlsx]
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
FileName:
Load
Month
Resident
FileName_Temp Order by MonthNo asc;
Drop Table FileName_Temp;
Let vRowCount = NoOfRows('FileName');
for i=0 to vRowCount-1
let vFileName = peek('Month',$(i),'FileName');
OpenSalesOrder:
LOAD
"Month",
"Order Date",
"Order No.",
"Delivery Week"
FROM [lib://Data/$(vFileName).xlsx]
(ooxml, embedded labels, table is Sheet1);
Next i;
Drop Table FileName;
After this script, you can add the new scripted field as traditional way.
I've also attached example app for your reference.
Hopefully, this will solve your problem.
Hi Chung,
Use Below script to load your files in specific order.
FileName_Temp:
Load
Distinct
Subfield(FileName(),'.',1) as Month,
Date(Date#(Subfield(FileName(),'.',1),'MMM'),'MM') as MonthNo
FROM [lib://Data/*.xlsx]
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
FileName:
Load
Month
Resident
FileName_Temp Order by MonthNo asc;
Drop Table FileName_Temp;
Let vRowCount = NoOfRows('FileName');
for i=0 to vRowCount-1
let vFileName = peek('Month',$(i),'FileName');
OpenSalesOrder:
LOAD
"Month",
"Order Date",
"Order No.",
"Delivery Week"
FROM [lib://Data/$(vFileName).xlsx]
(ooxml, embedded labels, table is Sheet1);
Next i;
Drop Table FileName;
After this script, you can add the new scripted field as traditional way.
I've also attached example app for your reference.
Hopefully, this will solve your problem.
Thanks for the reply.
I am still don't really understand your solution.
Normally I would just add the excel thru the "Data manager".
Should I directly using your suggested script to load the file?
Or Should I load the file in Data Manager first?
Besides that, what is [Next i;]?
Really appreciate your help. Thanks a million. ishanbhatt
Besides that, do i need to change the FileName_Temp: , FileName: ?
and how to open your attachment?
Sorry for asking a lot of question as I'm a new user for Qliksense.
I'm used to use Power BI which is totally different with Qliksense. Thanks for the help.
Hi Chung,
Yes, you can directly load my script just change the necessary things like connection name.
For this solution, you don't need to use Data Manager for data file loading.
Below are the steps to use above mentioned solution.
1. Create New Connection in the script editor. Give the connection path that point your files location.
2. Copy the script and change the file path in the script according to the path.
Now let's understand the logic.
/* Below logic is to get the month name as per your files. if there are three files Jan to Mar then it gives Jan, Feb, Mar in Month filed. Please note that here the Month name is not sorted */
FileName_Temp:
Load
Distinct
Subfield(FileName(),'.',1) as Month,
Date(Date#(Subfield(FileName(),'.',1),'MMM'),'MM') as MonthNo
FROM [lib://Data/*.xlsx]
(ooxml, embedded labels, table is Sheet1);
/* below script sort the month in ascending order. */
NoConcatenate //To avoid concatenation with FileName_Temp table.
FileName:
Load
Month
Resident
FileName_Temp Order by MonthNo asc;
/*Count the total number of row of the File Name table. If there are three files then in the FileName table Month field contains three row.*/
Let vRowCount = NoOfRows('FileName');
/* Now for loop in the market. We have the number of row in the vRowCount variable. Now we've to run the loop whatever number is stored in the vRowCount variable. */
/* We start for loop from 0 number row. we have three rows. Hence we have to write vRowCount-1).
for i=0 to vRowCount-1
/* It takes the row value e.g Jan, Feb, Mar */
let vFileName = peek('Month',$(i),'FileName');
/* Actual load the files. Please see the below From part where I put vFileName variable in the file name so every time when loop comes with the new row the same file is loaded from the location.*/
OpenSalesOrder:
LOAD
"Month",
"Order Date",
"Order No.",
"Delivery Week"
FROM [lib://Data/$(vFileName).xlsx]
(ooxml, embedded labels, table is Sheet1);
/* To continue loop for the next row I have written Next i. if in the first iteration i=0 then it loads Jan month file when it comes here it increments the I's value with 1. i=0+1 =1 up to the total number of rows (in your case max row count is 12).
Next i;
Drop Table FileName;
//By doing connection path and some other necessary changes you can use above logic directly.
Hope you understand all.
Thanks,
Ishan
Hi ishanbhatt,
Thanks for the explanation.
I guess I would need a tutorial class for that. Haha.
Few last questions:
1. What is FileName_Temp ?
2. So basically I do not have to change anything in your script except FROM [lib://Data/*.xlsx] and FROM [lib://Data/$(vFileName).xlsx]?
3. Just curious that the * symbol in FROM [lib://Data/*.xlsx] , does it means everything in the form of excel?
Once again, appreciate your explanation. You helped me a lot.
Besides that, is there any website or tutorial for me to learn about the scripting in Qliksense? I am interested in learning these scripting languages. Thank you.
Chung.
Hi Chung, maybe you only need to add date field in the order by clause:
Order by [Order No], [Order Date] desc;
desc is to order dates descendand, the first order is the 'new', others will be 'old'.
Hi Chung,
Answers of your questions.
1. What is FileName_Temp?
It is a table Name.
2. So basically I do not have to change anything in your script except FROM [lib://Data/*.xlsx] and FROM [lib://Data/$(vFileName).xlsx]?
Yes.
3. * means all files with xlsx extension in that folder.
Most probably you need to cover below items for scripting.
1. Different types of load statement (e.g Load, Binary load, Preceding load, mapping load, Resident load)
2. Mapping load, Apply Map
3. Interval Match
4. Set, Let to create Variable
5. Synthetic Keys, Synthetic Tables, Circular references
6. Looping
7. Conditions.
8. Sql concepts (joins, Where condition, Order by, Group by)
9. Link Table
10 Master Calendar
11. Qualify...Unqualify
12. Section Access
I doubt there is any material available to users that covers all the scripting. The QlikSense Help file covers most of it, but there are some undocumented features. You can also find a lot of information right here community.qlik.com. Just try searching first and if you can't find what you need feel free to ask.
Just download my attachment and drag the file into QlikSense and it will open.