Grouping Dates with consecutive weeks

Prajju
Prajju Member
in Magic ETL

Hi,

 

I have to visualize Projected Out of Stock Items and the date it goes out of stock in the inventory.

I have a table which shows when the item may go out of stock. This table shows the Projected Inventory for every Monday of the week.

 

Item

Location

Projected Inventory

Date

A1

L1

0

Aug 20, 2018

A1

L1

0

August 27, 2018

 

 A1

 L1

54

Sep 03,20-18

A1

L1

49

Sep 10, 2018

A1

L1

44

Sep 17

A1

L1

39

Sep 24

A1

L1

32

Oct 1

A1

L1

25

Oct 8

A1

L1

18

Oct 15

A1

L1

12

Oct 22

A1

L1

5

Oct 29

A1

L1

55

Nov 5

A1

L1

45

Nov 12

 

An item is considered to be Out of Stock if the : Projected Invenotry<20. Since the data is for every Monday of the week, it may take few weeks for the item to come back on stock(ie PI > 20).

As can be seen in the table, Item A1 goes out of stock on Aug 20, 2018 and comes back on stock on Sep 03, 2018. The output table desired is :

 

Item

Location

PI

Date Out of Stock

A1

L1

0

Aug 20, 2018

A1

L1

18

Oct 15, 2018

 

This is quite complex for me. Any help would be greatly appreciated.

 

Regards,
Prajwal

Best Answers

  • Valiant
    Valiant Coach
    Answer ✓

    So what I would do is first take your table of 4 columns and add a new column called 'Prior Week Inventory'. This gives you a baseline to compare against.

    In a SQL transform you could do something like this. (I'll call your current table datasetA)

    SELECT a.*,
    CASE WHEN   
    (SELECT b.`Projected Inventory` FROM datasetA AS b where DATE_SUB(a.`Date`, INTERVAL 7 DAY) = b.`Date`) IS NULL 
    THEN '20' 
    ELSE
    (SELECT b.`Projected Inventory` FROM datasetA AS b where DATE_SUB(a.`Date`, INTERVAL 7 DAY) = b.`Date`)
    END AS 'Prior Week Inventory'
    FROM datasetA AS a

    With your new dataset you can create a filter at the card level to remove the rows that don't apply.

     

    That would look like this:

    CASE WHEN `Prior Week Inventory` >= 20 AND `Projected Inventory` < 20 THEN 1 ELSE 0 END

    Set your filter to = 1 and that should give you the card you're looking for.

     

    Best of luck and let me know if you have any questions,

    Valiant

     

     

  • rado98
    rado98 Contributor
    Answer ✓

    If SQL is not your thing (like me) you can get the same new column using the lead function in magic ETL

  • rado98
    rado98 Contributor
    Answer ✓

    In magic ETL the function isin the Rank & Window file. Check out the knowledge base about it.

Answers

  • Valiant
    Valiant Coach
    Answer ✓

    So what I would do is first take your table of 4 columns and add a new column called 'Prior Week Inventory'. This gives you a baseline to compare against.

    In a SQL transform you could do something like this. (I'll call your current table datasetA)

    SELECT a.*,
    CASE WHEN   
    (SELECT b.`Projected Inventory` FROM datasetA AS b where DATE_SUB(a.`Date`, INTERVAL 7 DAY) = b.`Date`) IS NULL 
    THEN '20' 
    ELSE
    (SELECT b.`Projected Inventory` FROM datasetA AS b where DATE_SUB(a.`Date`, INTERVAL 7 DAY) = b.`Date`)
    END AS 'Prior Week Inventory'
    FROM datasetA AS a

    With your new dataset you can create a filter at the card level to remove the rows that don't apply.

     

    That would look like this:

    CASE WHEN `Prior Week Inventory` >= 20 AND `Projected Inventory` < 20 THEN 1 ELSE 0 END

    Set your filter to = 1 and that should give you the card you're looking for.

     

    Best of luck and let me know if you have any questions,

    Valiant

     

     

  • Prajju
    Prajju Member

    Hi,

     

    I am getting the following error when I run the SQL transform:

     "The database reported a syntax error. Subquery returns more than 1 row"

     

    Regards,

    Prajwal

     
  • rado98
    rado98 Contributor
    Answer ✓

    If SQL is not your thing (like me) you can get the same new column using the lead function in magic ETL

  • Prajju
    Prajju Member

    Hi, 

     

    Which function should i use exactly?

    I tried but could not figure it.

  • Prajju
    Prajju Member

    Can anyone help me out with this error?

    I am not able to figure it out.

  • rado98
    rado98 Contributor
    Answer ✓

    In magic ETL the function isin the Rank & Window file. Check out the knowledge base about it.

  • Prajju
    Prajju Member

    Thanks a lot. It worked like a charm.:)

     

     

Categories