Grouping Dates with consecutive weeks

Prajju

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

Valiant

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

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

rado98

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

Valiant

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

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

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

Prajju

Hi, 

 

Which function should i use exactly?

I tried but could not figure it.

Prajju

Can anyone help me out with this error?

I am not able to figure it out.

rado98

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

Prajju

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