Loading query

[#1]

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

[rahulgupta]

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

[#3]

You can see in the blog community Toccato. I posted about it today. Only in Portuguese.

Blog Toccato

Best Regards.

Tonial.

[#4]

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

[flipside]

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

[#6 · Accepted solution]

Helo James,

You can subscribe to the blog.

The following PDF file with the tutorial.

Best Regards.

Tonial.

[#7]

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