Grouping Dates with consecutive weeks

⚪️

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

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

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

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

• ⚪️

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

• ⚪️

Hi,

Which function should i use exactly?

I tried but could not figure it.

• ⚪️

Can anyone help me out with this error?

I am not able to figure it out.

• ⚪️

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