The arrangement of the loaded file affect the outcome

[tan_chungkam]

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.

[ishanbhatt · Accepted solution]

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.

[tan_chungkam]

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‌

[tan_chungkam]

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.

[ishanbhatt]

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

[tan_chungkam]

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.

[rubenmarin]

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'.

[ishanbhatt]

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.

[ishanbhatt]

Just download my attachment and drag the file into QlikSense and it will open.

[tan_chungkam]

Hi ishanbhatt‌,

Really thanks for your help.

Chung.