topic Re: Help with IntervalMatch in QlikView

Help with IntervalMatch

Wed, 18 Sep 2013 22:09:09 GMT

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?

Re: Help with IntervalMatch

CELAMBARASAN — Wed, 18 Sep 2013 22:54:42 GMT

Hi,

Hope the attachment helps you.

Re: Help with IntervalMatch

srchilukoori — Thu, 19 Sep 2013 01:35:00 GMT

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

Re: Help with IntervalMatch

Thu, 19 Sep 2013 12:57:29 GMT

Thanks a lot, this was very good!

Re: Help with IntervalMatch

Thu, 19 Sep 2013 23:04:06 GMT

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!

Re: Help with IntervalMatch

CELAMBARASAN — Thu, 19 Sep 2013 23:09:24 GMT

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

Thu, 19 Sep 2013 23:21:22 GMT

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

CELAMBARASAN — Thu, 19 Sep 2013 23:32:58 GMT

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

Re: Help with IntervalMatch

Fri, 20 Sep 2013 08:15:27 GMT

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