loading and aggregating few excel sheets

[shayraber]

hi all,

I'm new with QV and I'm facing an issue regarding loading of an excel file with 2 sheets needed to be aggregated into one table.

asuming i have a sheet like this:

ID	Name	Amount	Type
1	AAA	2	A
2	BBB	12	A
3	CCC	0	A
...	...	....	....

and the second sheet looks like this:

ID	Name	Amount	Type
1	AAA	0	B
	BBB	454	B
3	CCC	77	B
....	....	....	....

and the result input talbe at the QV should look like this:

ID	Name	Amount
1	AAA	2 (=2+0)
2	BBB	466 (=12+454)
3	CCC	77 (=0+77)
....	.....	.....

how can it be done?

thanks and best regards,

Shay

[Miguel_Angel_Baeyens · Accepted solution]

Hi Shay,

In this case I'd use the ApplyMap() function to take values under Amount in sheet one and add to sheet two, creating a table with both values. The script should look like the following code:

FirstSheetMap:
MAPPING LOAD ID & '/' & Name AS Key
     Amount
FROM
File.xls
(biff, embedded labels, table is Sheet1$);
TableWithTotals:
LOAD ID,
     Name,
     ApplyMap('FirstSheetMap', ID & '/' & Name) + Amount AS TotalAmount,
     Type
FROM
File.xls
(biff, embedded labels, table is Sheet1000$);

Hope that helps.

Miguel

[shayraber]

Miguel,

THANKS A LOT!!!