topic Re: Loading query in QlikView

Loading query

Mon, 01 Aug 2011 12:28:57 GMT

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

Loading query

rahulgupta — Mon, 01 Aug 2011 12:37:57 GMT

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

Re: Loading query

Mon, 01 Aug 2011 17:42:12 GMT

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

Blog Toccato

Best Regards.

Tonial.

Re: Loading query

Tue, 02 Aug 2011 10:47:25 GMT

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

Re: Loading query

flipside — Tue, 02 Aug 2011 11:29:37 GMT

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

Re: Loading query

Tue, 02 Aug 2011 12:16:42 GMT

Helo James,

You can subscribe to the blog.

The following PDF file with the tutorial.

Best Regards.

Tonial.

Re: Loading query

Tue, 02 Aug 2011 13:49:00 GMT

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