topic Re: Data from second table (UOM) in QlikView

Data from second table (UOM)

prma7799 — Wed, 27 Dec 2017 04:38:55 GMT

Dear All,

I have two table one is ITEM and second is UOM.

ITEM table has Item Code & UOM Code (Unit Of Measure Code) in this one Item Code have one UOM

In UOM table have same item code which is present in ITEM table but here one Item Code have multiple UOM called SecondUOM.

So my requirement is

If Itemcode have STRIP in ITEM table then I want GMS Qty from UOM table

If Itemcode have BOTTLE in ITEM table then I want LTR Qty from UOM table

If Itemcode have TUBE in ITEM table then I want GMS Qty from UOM table

If Itemcode have Bags in ITEM table then I want GMS Qty from UOM table

for that perticular ItemCode.

PFA sample data.

Re: Data from second table (UOM)

tresB — Wed, 27 Dec 2017 05:17:40 GMT

Could you share your expected out too?

Re: Data from second table (UOM)

qliksus — Wed, 27 Dec 2017 05:30:39 GMT

Not sure whether I got it clearly but if you have the itemcode connected between the 2 tables then the below expression would do

Sum({<itemcode={'STRIP','tube','bags'}>}GMS Qty) + Sum({<itemcode={'bottle'}>}Ltr Qty)

Re: Data from second table (UOM)

prma7799 — Wed, 27 Dec 2017 05:31:41 GMT

PFA for SAMPLE output

Re: Data from second table (UOM)

Anil_Babu_Samineni — Wed, 27 Dec 2017 05:37:16 GMT

Perhaps this?

ITEM:

Load [Item Code], [UOM Code] From ITEM;

UOM:

Load [Item Code], SecondUOM, [UOM Qty] From UOM;

Left Join (UOM)

Load *, If([UOM Code] = SecondUOM, 'Yes', 'No') as Flag, Sum([UOM QTY]) as [GMS Qty] Resident ITEM Where Match([UOM Code], 'STRIP') Group By <All Non Aggregate Fields>;

Concatenate(Final)

Load *, If([UOM Code] = SecondUOM, 'Yes', 'No') as Flag, Sum([UOM QTY]) as [LTR Qty] Resident ITEM Where Match([UOM Code], 'BOTTLE') Group By <All Non Aggregate Fields>;

Concatenate(Final)

Load *, If([UOM Code] = SecondUOM, 'Yes', 'No') as Flag, Sum([UOM QTY]) as [GMS Qty] Resident ITEM Where Match([UOM Code], 'TUBE', 'Bags') Group By <All Non Aggregate Fields>;

Note - I've not tested due to s/w not installed in my live location.

Re: Data from second table (UOM)

tresB — Wed, 27 Dec 2017 05:58:24 GMT

Try like:

if(Max(Match([UOM Code],'STRIP','TUBE','BAGS')), Sum(if(SecondUOM='GMS',[UOM QTY])),

If(Max(Match([UOM Code],'BOTTLE')), Sum(if(SecondUOM='LTR',[UOM QTY]))))

Re: Data from second table (UOM)

prma7799 — Wed, 27 Dec 2017 06:38:44 GMT

Hi Tresesco ,

Using your logic am getting correct output but i have many UOM Code like below

AMPOULE

BAGS

BOTTLE

CONTAINER

GMS

GRAMS

KGS

KIT

LTR

NOS

POUCH

STRIP

TUBE

VIAL

So my question is what should i do for others using above logic am getting only for 'STRIP','TUBE','BAGS' UOM data ...

Re: Data from second table (UOM)

tresB — Wed, 27 Dec 2017 06:56:13 GMT

If you want this in UI, I am afraid that you have to extend the expression with similar logic for rest groups of your codes. However, dealing it in the script would probably be a better solution provided your logic doesn't change very often. For script solution you could think in line of maintaining a mapping table and then using ApplyMap().

Re: Data from second table (UOM)

prma7799 — Wed, 27 Dec 2017 06:59:29 GMT

Could you please share code for back end also?