Data from second table (UOM)

[prma7799]

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.

[tresesco]

Could you share your expected out too?

[qliksus]

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)

[prma7799]

PFA for SAMPLE  output

[Anil_Babu_Samineni]

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.

[tresesco · Accepted solution]

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]))))

[prma7799]

Hi Tresesco ,

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

AMPOULE

BAGS

BOTTLE

CONTAINER

GMS

GRAMS

KG

KGS

KIT

LTR

MG

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 ...

[tresesco]

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().

[prma7799]

Could you please share code for back end also?