Map field to other table

[Nagaraju_KCS]

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

[avinashelite]

Try like this

Type_Mapping:

Mapping LOAD

CostCentre&'-'&ClientCode&'-'&Year as Key,

Type

from table2;

Table1:

LOAD

CostCentre,

Account,

ClientCode,

Amount,

Year,

applymap('Type_Mapping',CostCentre&'-'&ClientCode&'-'&Year,'NA') as Type

from

table1;

[Nagaraju_KCS]

this is correct, but if i have any shuffle data..

it will wrong.

[avinashelite]

Did you tried my suggestion

[#5]

Hi,

May be like this,

Create a key With combination of rec no in both tables, then do applemap,

tt:

Mapping LOAD

//CostCentre,

CostCentre&ClientCode&year&RecNo() as key,

Type

Resident Table2;

DROP Table Table2;

map:

LOAD *,

RecNo(),

ApplyMap('tt',CostCentre&ClientCode&Year&RecNo(),'') as type

Resident Table1;

DROP Table Table1;

You can use order by in both table if you have suffle data.

May be this will work try it

[jonathandienst]

qv_testing wrote:

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.

You don't have enough information to do what you ask. Costcentre, ClientCode and Year do not identify individual rows in either table, so you cannot associate a single Type with each row in Table1. And you will not be able to produce your output table without some additional information.