Automate data load from files and more

[betz]

Hi

I'm looking for a solution for the following:

1.  Every month we create 3 xls file. For this matter let's call them StepA, StepB, StepC.

2.  Each file represent detailed data of sales for the month.

3.  Each file must be loaded in the order of receiving and override the previous one.

4.  At the end,  I want to show a final report which summarizes (maybe in a pivot style) what described in the image attached.

5. So there are 3 main concerns here:
    -   loading the files dynamically for a given month.
    -   dynamically add the month column to the sheet as in the attached image.
    -   providing a layout like in the attached image.

[rwunderlich]

You can create the Month value dynamically based on the date of load execution or the date of the file being loaded. For example:

Date(MonthStart(Today(1)), 'MMM-YY') as Month

Date(MonthStart(FileTime()), 'MMM-YY') as Month

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[betz]

Thank you.

Is there a way to dynamically loop through a folder or I must know of each file in advance?

Also, can you advise re my other questions? re the layout etc?

Tx

[JonnyPoole]

There is a classic sample to load all files in a directory. 

There are a lot of variants but this uses just 1 folder and looks for *.log and loads all fields from all matching files using specific encoding, delimiters etc...

In your case, I suggest loading one file manually just to get the options right. 

I strongly suggest naming all the columns and filtering out all files that don't match your need. 

You can add other fields like filetime() as well to the LOAD of course and conduct the rest of the required logic. 

FOR each File in filelist ('[lib://<folder>/*.' & 'log]')

   LOAD 

      *

   FROM '$(File)'

   (txt, codepage is 28591, embedded labels, delimiter is '\t', msq);

next File

[betz]

How would I go with a pivot table or anything similar described in my screenshot?

I want to achieve a similar layout like in the below image, where for each month I show the quantity and sales amount

by product name and product description.

Any recommendation?  How do I get this If I have a file per month?

[JonnyPoole]

First you need to load all the files into 1 table in the data model using table concatenation. Practice trying to do that in the load script with two of your XLS files. If you can do that successfully, then you can change the FOR loop sample above to your needs so as to get all the XLS files in the directory at once.  Once you have that working, you can author a pivot table in the UI. 

[betz]

Thank you JonnyPoole

So by saying concatenation you mean a join?

Let's assume these are the files I have:

Products.xlsx:

ProductID (PK),ProductName, Price

JanaurySales.xlsx:

SaleID (PK) , ProductID  (FK) ,Qty 

FebruarySales.xlsx:

SaleID (PK) , ProductID  (FK) ,Qty 

(and so on for other months...)

How do I perform the concatenation between all the Loads? 

Does Qlik know to treat each file joined to the Products file as a different join or would it join

the Products >> Jan >> Feb... etc?  For example If I had to do it in SQL then I would have a reference to the Products for each month... wonder if Qlik treats it differently

[JonnyPoole]

Table concatenation is like 'stacking' all the rows from one LOAD with all the rows from another LOAD. If the fields are named the same, the values from each LOAD are aligned into the right columns. In SQL its like a UNION.  In your case  you want to loop through all the *Sales.xlsx files in the folder and CONCATENATE (ie: union) all the rows together into one 'Sales' table in your data model. Load the Products.xlsx separately to create a separate 'Products' table.  

You can probably rely on "automatic concatenation" as described here

https://help.qlik.com/en-US/sense/February2024/Subsystems/Hub/Content/Sense_Hub/LoadData/concatenate...

[betz]

Thanks for that.

So create the pivot table like the below should be quite easy?