Map field to other table

[qv_testing]

Hi Community,

I want to map field one table to another table.

please look at here..

Suppose i have 2 tables..

Table1:

CostCentre	Account	ClientCode	Amount	Year
5110	170110	366001	1245	2014
1915	110311	ABCD	1000	2014
1915	110711	ABCD	12	2014
1915	110311	ABCD	123	2014
1915	110311	ABCD	45	2014
1915	110311	ABCD	80	2014
1915	110711	ABCD	30	2014

Table2:

CostCentre	ClientCode	Type	Amount	year
5110	366001	WIP IN	1245	2014
1915	ABCD	WIP OUT	1000	2014
1915	ABCD	Notes	12	2014
1915	ABCD	ABC	123	2014
1915	ABCD	SUMAC	0	2014
1915	ABCD	WIP IN	89	2014
1915	ABCD	WIP OUT	9	2014
1916	ABVF	ABVH	19	2014

These are my 2 tables, Table1 is main data, Table2 is also almost same,

but Amount different.

Here i have to map Type field to Table1, i don't bother about Amount in second table.

I want to Table1 Amount  only.

Note: Only i have to Map Type field to Table1.

My Output should be, If i am take

Dimension: Type

Expression: Sum({<Year={'2014'}>}Amount)

Type	2014
WIP IN	1325
WIP OUT	1030
Notes	12
ABC	123
SUMAC	45

Hope you Understand....

Thanks in Advance..

Map field to other table

[Kushal_Chawda]

Simply link you tables by creating Key like this

Table1:

LOAD ID,

           autonumber(Client&Category&Amount) as Key

          Client,

          Category,

          Product,

          Amount

FROM table1;

Table2:

LOAD autonumber(Client&Category&Amount) as Key,

          Order

FROM table2;

[avinashelite]

if you have multiple mapping values like 

ID	Client	Category	Product	Amount
1	AC001	Poker Paul	Nokia	1290
2	AC001	Poker Paul	Nokia	-1290

Don't go with the Mapping function simple create a common key field between tables

Table1:

LOAD ID,

           Client&'-'&Category&'-'&Amount as Key

          Client,

          Category,

          Product,

          Amount

FROM table1;

Table2:

LOAD Client&'-'&Category&'-'&Amount as Key,

          Order

FROM table2;

[qv_testing]

HI Kaushal/Avinash,

please look at here..

Suppose i have 2 tables..

Table1:

CostCentre	Account	ClientCode	Amount	Year
5110	170110	366001	1245	2014
1915	110311	ABCD	1000	2014
1915	110711	ABCD	12	2014
1915	110311	ABCD	123	2014
1915	110311	ABCD	45	2014
1915	110311	ABCD	80	2014
1915	110711	ABCD	30	2014

Table2:

CostCentre	Account	ClientCode	Type	Amount	year
5110	170110	366001	WIP IN	1245	2014
1915	110311	ABCD	WIP OUT	1000	2014
1915	110711	ABCD	Notes	12	2014
1915	110311	ABCD	ABC	123	2014
1915	110311	ABCD	SUMAC	0	2014
1915	110311	ABCD	WIP IN	89	2014
1915	110711	ABCD	WIP OUT	9	2014

These are my 2 tables, Table1 is main data, Table2 is also almost same,

but Amount different.

Here i have to map Type field to Table1, i don't bother about Amount in second table.

I want to Table1 Amount  only.

Note: Only i have to Map Type field to Table1.

My Output should be, If i am take

Dimension: Type

Expression: Sum({<Year={'2014'}>}Amount)

Type	2014
WIP IN	1325
WIP OUT	1030
Notes	12
ABC	123
SUMAC	45

Hope you Understand....

Thanks in Advance..

[qv_testing]

Please guys have a look above post...

[MindaugasBacius]

map_type:
mapping LOAD autonumber(CostCentre & ClientCode & Amount & year),
     Type
FROM

(ooxml, embedded labels, table is Sheet2);


Master:
LOAD CostCentre, 
     Account, 
     ClientCode, 
     Amount, 
     Year,
     ApplyMap('map_type', autonumber(CostCentre & ClientCode & Amount & Year), ':') as Type
FROM

(ooxml, embedded labels, table is Sheet1);

The outcome would be like this:

Take a look at the attachment.

[qv_testing]

But here i don't have Amount same in 2 tables.

I want to fetch from Amount Table1.

My Output should be

Dimension: Type

Expression: Sum({<Year={'2014'}>}Amount)

Type	2014
WIP IN	1325
WIP OUT	1030
Notes	12
ABC	123
SUMAC	45

[MindaugasBacius]

Maybe I misunderstood something:

Here i have to map Type field to Table1, i don't bother about Amount in second table.

I want to Table1Amount  only.

What are the conditions again please?

[#9]

Hi

See the attached,

[qv_testing]

in my second table Amount someone entered manually,

So values are not correct.

Only i need to fetch Table1 Amount Only.

i want to compare with Costcentre, CLientCode and Year.

I have to Map Type field to Table1.