Thanks for the responses. Unfortunately I am unable to access the linked blog.
My initial question was a little unclear, so I will clarify what I am trying to achieve.
I have multiple spreadsheets each for a different client in the following format:Client
AAAA Date Balance Fee 23-May 50 100 24-May 60 120 25-May 100 200 26-May 50 100 27-May 40 80 28-May 100 200 29-May 50 100
I am looking for some code that will pick up the client in cell B1 and produce a Qlikview table as follows:
Client Date Balance Fee AAAA 23-May 50 100 AAAA 24-May 60 120 AAAA 25-May 100 200 AAAA 26-May 50 100 AAAA 27-May 40 80 AAAA 28-May 100 200 AAAA 29-May 50 100
Try something like this, basically just reads the top line of the spreadsheet, passes the value of col B to a variable to use in the secondary load. All the sheets will need to be same format etc..
Temp: LOAD A, //col A B, //col B C //col C FROM [Clients.xlsx] (ooxml, no labels, table is Sheet1) Where rowno()=0; LET vClient=FieldValue('B',1); DROP TABLE Temp; Data: LOAD '$(vClient)' as Client, A as Date, B as Value, C as Fee FROM [Clients.xlsx] (ooxml, no labels, header is 4 lines, table is Sheet1);
You can subscribe to the blog.
The following PDF file with the tutorial.