topic Production Vs Manufacturing Issue in QlikView

Production Vs Manufacturing Issue

siva0606 — Fri, 05 Apr 2013 07:48:30 GMT

Hi all,

I have a "HL" type code in Production table

and "S"type code in Manufacturing table

Both Prod and MFG is linked by ItemCodekey(ItemCode&'-'&Year&'-'&Month)

Production:

ItemCode	ProductionQty
WNU012H	268542
WNU012L	27517
VNU012H	21780
VNU012L	20764

Manufacturing:

ItemCode	Manufacturing Qty
WNU012S	28063
VNU012S	21511

Most important is I need to split the Item Code in MFG table

WNU012S as WNU012H and WNU012L

Similarly,VNU012S as VNU012H and VNU012L and both H and L should get the same value of S

and my final table should look like this

ItemCode	ProductionQty	Manufacturing Qty	To be Packed
WNU012H	26842	28063	-1221
WNU012L	27517	28063	-546
VNU012H	21780	21511	269
VNU012L	20764	21511	-747

FYI: To be Packed= ProdQty-MFGQty

With Kind Regards,

Shiva

Re: Production Vs Manufacturing Issue

alexandros17 — Fri, 05 Apr 2013 08:03:45 GMT

Try this code, you can optimize it with a loop but it works as is:

Production:

LOAD * Inline

[ItemCode,ProductionQty

WNU012H,268542

WNU012L,27517

VNU012H,21780

VNU012L,20764

];

Manufacturing_tmp:

NoConcatenate

LOAD * Inline

[ItemCode,ManufacturingQty

WNU012S,28063

VNU012S,21511

];

Manufacturing:

NoConcatenate

LOAD

if(right(ItemCode,1)='S',left(ItemCode,len(ItemCode)-1) & 'H',ItemCode) as ItemCode,

ManufacturingQty

Resident Manufacturing_tmp;

Concatenate

LOAD

if(right(ItemCode,1)='S',left(ItemCode,len(ItemCode)-1) & 'L',ItemCode) as ItemCode,

ManufacturingQty

Resident Manufacturing_tmp;

DROP Table Manufacturing_tmp;

Re: Production Vs Manufacturing Issue

siva0606 — Fri, 05 Apr 2013 08:50:33 GMT

It works.Thank you so much.