Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
You can use applymap if you have huge data.
i have huge data with many tables linked to PO table. could you help me with the applymap solution?
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 ];
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;
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;