10 Replies Latest reply: Jul 12, 2018 9:22 PM by Chung Kam Tan RSS

    The arrangement of the loaded file affect the outcome

    Chung Kam Tan

      I have 4 excel files which loaded as below:

       

      Capture.PNG

       

      I created 2 new columns by scripting as shown below:

      Capture.PNG

       

      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:

      Capture.PNG

      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.

        • Re: The arrangement of the loaded file affect the outcome
          ishan Bhatt

          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.

            • Re: The arrangement of the loaded file affect the outcome
              Chung Kam Tan

              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

                • Re: The arrangement of the loaded file affect the outcome
                  ishan Bhatt

                  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

                    • Re: The arrangement of the loaded file affect the outcome
                      Chung Kam Tan

                      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.

                        • Re: The arrangement of the loaded file affect the outcome
                          ishan Bhatt

                          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.





                    • Re: The arrangement of the loaded file affect the outcome
                      Chung Kam Tan

                      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.


                    • Re: The arrangement of the loaded file affect the outcome
                      Ruben Marin

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