Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See WhyThousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
fallenangel6
Creator
Creator

item wise data display

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_iditem_idinv_amt
11A1200
11B100
22A1000
22B200
22C100

 

Advance table:

 

po_idadvance
1500
2200

 

 

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

 

po_iditem_idinv_amtadvance
11A1200500
11B100-
22A1000200
22B200-
22C100-

 

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.

7 Replies
pradosh_thakur
Master II
Master II

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;
Learning never stops.
pradosh_thakur
Master II
Master II

You can use applymap if you have huge data.

Learning never stops.
fallenangel6
Creator
Creator

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

pradosh_thakur
Master II
Master II

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
];
Learning never stops.
fallenangel6
Creator
Creator

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
Master II
Master II

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;
Learning never stops.
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;