Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need to load multiple data tables containing the same fields, each for a different client, example below:
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 |
Anyone know of a way when loading of adding the client name to each row of data in the table without having to go into each sheet and adding an additional 'client' column as below?
Client | AAAA | ||
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 |
thanks in advance,
James
Helo James,
You can subscribe to the blog.
The following PDF file with the tutorial.
Best Regards.
Tonial.
Hey hi,
Just Use the Code as follows:
Load *,
'AAAA' as ClientName
From Table;
Load*,
'BBBB' as ClientName
From Table;
and so on..........
Hope this helps...
Regards
You can see in the blog community Toccato. I posted about it today. Only in Portuguese.
Best Regards.
Tonial.
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 |
Thanks again,
James
Hi James
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);
flipside
Helo James,
You can subscribe to the blog.
The following PDF file with the tutorial.
Best Regards.
Tonial.
Thanks guys,
Have just followed the steps in the PDF and it does exactly what I want, much appreciated.
Will give the other method a try too.
Cheers,
James