Help with IntervalMatch

[#1]

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?

[CELAMBARASAN · Accepted solution]

Hi,

Hope the attachment helps you.

[srchilukoori]

Interval match is not needed to create the stock chart. Check the attached app.

[#4]

Thanks a lot, this was very good!

[#5]

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!

[CELAMBARASAN]

Something like

if(AddMonths(Date(Date#(REGISTERED_DATE, 'YYYYMM')), IterNo() - 1) = Date(Date#(SOLD_DATE_SIM, 'YYYYMM')),1,0) AS FlagSold

[#7]

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'));

[CELAMBARASAN]

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'));

[#9]

That solved it. Thank you Celambarasan for your great contribution!