item wise data display

[fallenangel6]

Hi,

i have below data. every po id have multiple line item with itemwise data and against every po id, i have some advance amount which is not item wise as shown in below table.

PO table

po_id	item_id	inv_amt
1	1A	1200
1	1B	100
2	2A	1000
2	2B	200
2	2C	100

Advance table:

po_id	advance
1	500
2	200

now i want this advance against the PO table as below.

po_id	item_id	inv_amt	advance
1	1A	1200	500
1	1B	100	-
2	2A	1000	200
2	2B	200	-
2	2C	100	-

in first row for each po id, i need to show the advance amount and in other rows for same po id, it should be either '-' or 0.

Please help.

[pradosh_thakur]

Try below

load *, if(peek(po_id)=po_id,null(),po_id & '_' & 1 ) as join_key inline [
po_id,item_id,inv_amount
1,1A,1200
1,1B,100
2,2A,1000
2,2B,200
2,2C,100
];

left join

LOAD po_id & '_' & 1 as join_key,advance Inline [
po_id,advance
1,500
2,200
];

drop field join_key;

[pradosh_thakur]

You can use applymap if you have huge data.

[fallenangel6]

i have huge data with many tables linked to PO table. could you help me with the applymap solution?

[pradosh_thakur]

Mapping:
mapping LOAD po_id & '_' & 1 as join_key,advance Inline [
po_id,advance
1,500
2,200
];


load *,applymap('Mapping',join_key,null())  as advance;
load *, if(peek(po_id)=po_id,null(),po_id & '_' & 1 ) as join_key  inline [
po_id,item_id,inv_amount
1,1A,1200
1,1B,100
2,2A,1000
2,2B,200
2,2C,100
];

[fallenangel6]

what if my data is this way
1,1A,1200
2,2A,1000
2,2B,200
2,2C,100
1,1B,100

Here 1,1B,100 record has advance mapped to it.. which is wrong..

[pradosh_thakur]

Mapping:
mapping LOAD po_id & '_' & 1 as join_key,advance Inline [
po_id,advance
1,500
2,200
];

abc:
load * inline [
po_id,item_id,inv_amount
1,1A,1200
1,1B,100
2,2A,1000
2,2B,200
2,2C,100
1,1B,300
];
noconcatenate
load *,applymap('Mapping',join_key,null())  as advance;
load *, if(peek(po_id)=po_id,null(),po_id & '_' & 1 ) as join_key  
resident abc
order by po_id;

DROP TABLE abc;

[sunny_talwar]

You can further fine tune this a little by getting rid of the preceding load here

MappingTable:
Mapping
LOAD po_id & '|' & 1,
	 advance;
LOAD * INLINE [
    po_id, advance
    1, 500
    2, 200
];

PO:
LOAD * INLINE [
    po_id, item_id, inv_amt
    1, 1A, 1200
    1, 1B, 100
    2, 2A, 1000
    2, 2B, 200
    2, 2C, 100
];

FinalPO:
LOAD *,
	 If(po_id = Previous(po_id), RangeSum(Peek('No'), 1), 1) as No,
	 ApplyMap('MappingTable', po_id& '|' & If(po_id = Previous(po_id), RangeSum(Peek('No'), 1), 1), Null()) as advance
Resident PO
Order By po_id, item_id;

DROP Table PO;