Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need some help with a problem I had for a while to calculate how many cars we have on stock each month.
I have a source-table that looks like this:
Contract | RegistrationDate | SoldDate |
10001 | 201201 | 201203 |
10002 | 201202 | 201205 |
10003 | 201204 | 201204 |
10004 | 201203 |
Each contract represents a car. RegistrationDate is when we received the car and SoldDate is when we sold the car. If SoldDate is empty it means the car is still on stock.
I’m trying to get a stock-table that looks like this where I flagged each month the car is on stock:
Contract | 201201 | 201202 | 201203 | 201204 | 201205 |
10001 | 1 | 1 | 0 | 0 | 0 |
10002 | 0 | 1 | 1 | 1 | 0 |
10003 | 0 | 0 | 0 | 0 | 0 |
10004 | 0 | 0 | 1 | 1 | 1 |
I tried the IntervalMatch and connect the table to a Calendar without results.
Can someone please help me with this?
Hi,
Hope the attachment helps you.
Interval match is not needed to create the stock chart. Check the attached app.
Thanks a lot, this was very good!
Hi,
Do someone of you also know how I can in the loop set a flag when the cars been sold?
Here is the script:
Table:
LOAD
CONTRACT,
REGISTERED_DATE,
SOLD_DATE,
Date(AddMonths(Date(Date#(REGISTERED_DATE, 'YYYYMM')), IterNo() - 1), 'YYYYMM') AS DimDate,
1 AS FlagStock,
IF(IterNO() = 1, 1, 0) AS FlagEntries
// IF(statement,1,0) AS FlagSold
Resident Table
While AddMonths(Date(Date#(REGISTERED_DATE, 'YYYYMM')), IterNo() - 1) < Date(Date#(SOLD_DATE_SIM, 'YYYYMM'));
----------------------------------------------------
So the FlagSold should be on the month after the last stockmonth if the car is sold. Is that possible in this loop?
This is what I'm looking for:
Contract | DimDate | FlagEntries | FlagStock | FlagSold |
1001 | 201205 | 1 | 1 | 0 |
1001 | 201206 | 0 | 1 | 0 |
1001 | 201207 | 0 | 1 | 0 |
1001 | 201208 | 0 | 0 | 1 |
1001 | 201209 | 0 | 0 | 0 |
1001 | 201210 | 0 | 0 | 0 |
Here the car was received 201205 and sold in 201208. FlagEntries and FlagStock works fine in the loop.
Thanks again!
Something like
if(AddMonths(Date(Date#(REGISTERED_DATE, 'YYYYMM')), IterNo() - 1) = Date(Date#(SOLD_DATE_SIM, 'YYYYMM')),1,0) AS FlagSold
I tried that but I only get 0 on FlagSold. I think it's because of the small change I had to make to get the stockflag not to include the month the car was sold.
Changed: <= to <
While AddMonths(Date(Date#(REGISTERED_DATE, 'YYYYMM')), IterNo() - 1) < Date(Date#(SOLD_DATE_SIM, 'YYYYMM'));
You can change it to
Table:
LOAD
CONTRACT,
REGISTERED_DATE,
SOLD_DATE,
Date(AddMonths(Date(Date#(REGISTERED_DATE, 'YYYYMM')), IterNo() - 1), 'YYYYMM') AS DimDate,
if(AddMonths(Date(Date#(REGISTERED_DATE, 'YYYYMM')), IterNo() - 1) < Date(Date#(SOLD_DATE_SIM, 'YYYYMM')),1,0) AS FlagStock,
IF(IterNO() = 1, 1, 0) AS FlagEntries,
if(AddMonths(Date(Date#(REGISTERED_DATE, 'YYYYMM')), IterNo() - 1) = Date(Date#(SOLD_DATE_SIM, 'YYYYMM')),1,0) AS FlagSold
Resident Table
While AddMonths(Date(Date#(REGISTERED_DATE, 'YYYYMM')), IterNo() - 1) <= Date(Date#(SOLD_DATE_SIM, 'YYYYMM'));
That solved it. Thank you Celambarasan for your great contribution!