Production Vs Manufacturing Issue

[siva0606]

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

[alexandros17 · Accepted solution]

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;

[siva0606]

It works.Thank you so much.