Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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...
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?
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.
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
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
Thanks for that.
So create the pivot table like the below should be quite easy?