8 Replies Latest reply: Sep 20, 2013 4:15 AM by Johan Ohlsson

# Help with IntervalMatch

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.

• ###### Re: Help with IntervalMatch

Hi,

Hope the attachment helps you.

• ###### Re: Help with IntervalMatch

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

• ###### Re: Help with IntervalMatch

Thanks a lot, this was very good!

• ###### Re: Help with IntervalMatch

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:

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!

• ###### Re: Help with IntervalMatch

Something like

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

• ###### Re: Help with IntervalMatch

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

• ###### Re: Help with IntervalMatch

You can change it to

Table:

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

• ###### Re: Help with IntervalMatch

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