Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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.

Can someone please help me with this?

Tags (1)
1 Solution

Accepted Solutions

Re: Help with IntervalMatch

Hi,

Hope the attachment helps you.

8 Replies

Re: Help with IntervalMatch

Hi,

Hope the attachment helps you.

srchilukoori
Valued Contributor

Re: Help with IntervalMatch

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

Not applicable

Re: Help with IntervalMatch

Thanks a lot, this was very good!

Not applicable

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:

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:

ContractDimDateFlagEntriesFlagStockFlagSold
1001201205110
1001201206010
1001201207010
1001201208001
1001201209000
1001201210000

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

Not applicable

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:

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

Not applicable

Re: Help with IntervalMatch

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

Community Browser