Help with Magic ETL to find the status of SKU items manufactured

ozarkram
ozarkram Member
in Magic ETL
Sample data - Copy.xlsx

I have a delay table (as below) where a SKU is identified by the combination of mmodel and srno and the snapdate.. A SKU present on a specific snapdate indicates the SKU was delayed on that date

Delay Table

mmodel  Srno    Snapdate
MX201   C12341  3/21/2021
NM213   L1234   3/21/2021
JK1231  K1561   3/21/2021
KL1234  MJ1234  3/21/2021
MX201   C12341  3/22/2021
NM213   L1234   3/22/2021
OP1231  JK123   3/22/2021
MB321   LK123   3/22/2021
MX201   C12341  3/23/2021
BX378   H1231   3/23/2021
LZ231   MD321   3/23/2021
LO3412  LP1231  3/23/2021
MX201   C12341  3/24/2021
BX378   H1231   3/24/2021
FE221   J6571   3/24/2021

Mfg table

mfgdate mmodel  Srno
3/21/2021   JK123   K21310
3/21/2021   KL378   L8941
3/22/2021   JK1231  K1561
3/22/2021   KL1234  MJ1234
3/22/2021   KL918   S21367
3/23/2021   NM213   L1234
3/23/2021   OP1231  JK123
3/23/2021   MB321   LK123
3/23/2021   XC2312  M89321
3/24/2021   LZ231   MD321
3/24/2021   LO3412  LP1231
3/24/2021   KL5612  D3489

The above table has the mfgdate (date when the SKU got manufactured)..Now I want to create a status table (as below) and ignore the first date in the above two tables and start from the second date..

Status output table

Date    mmodel  Srno    SKUComplete     Goodstock   offdelay    NewDelay
3/22/2021   JK1231  K1561   1   0   1   0
3/22/2021   KL1234  MJ1234  1   0   1   0
3/22/2021   KL918   S21367  1   1   0   0
3/22/2021   OP1231  JK123   0   0   0   1
3/22/2021   MB321   LK123   0   0   0   1
3/23/2021   NM213   L1234   1   0   1   0
3/23/2021   OP1231  JK123   1   0   1   0
3/23/2021   MB321   LK123   1   0   1   0
3/23/2021   XC2312  M89321  1   1   0   0
3/23/2021   BX378   H1231   0   0   0   1
3/23/2021   LZ231   MD321   0   0   0   1
3/23/2021   LO3412  LP1231  0   0   0   1
3/24/2021   LO3412  LP1231  1   0   1   0
3/24/2021   KL5612  D3489   1   1   0   0
3/24/2021   FE221   J6571   0   0   0   1

The above table is got by looking up the mfg table first and the SKU present in the mfg table would be marked as SKUComplete 1 ..Now the same SKU should be looked up in the delay table on the previous date (Mfgdate-1)..If the SKU is present in the previous date in the delay table then offdelay would become 1 otherwise goodstock would become 1

After completing all the SKU'S in the mfg table for a particular manufacturing date the remaining SKU'S should be looked up in the delay table for the same date (non matching SKU'S) and they should be entered in the Status table with New delay as 1

Tagged:

Answers

  • ozarkram
    ozarkram Member

    To summarize

    1. The SKU from the mfg table and date should be taken and if the same SKU is present in the immediate previous date in the delay table then its status would be complete -1 and offdelay -1
    2. The SKU from the mfg table and date should be taken and if the same SKU is not present in the immediate previous date in the delay table then its status would be complete -1 and goodstock-1
    3. The SKU that is not present in mfg table on a specific date but is present in the delay table on that specific date (but the same SKU is not present in the immediate previous date in the delay table then its status would be just new delay -1

    4)The SKU that is not present in mfg table on a specific date but is present in the delay table on that specific date (but the same SKU is present in the immediate previous date in the delay table then that SKU won't be in the output at all for that date...

Categories