Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
It works.Thank you so much.