topic Re: Intervalmatch issue in Qlik Learning Discussions

Intervalmatch issue

Anonymous — Mon, 15 Sep 2014 05:08:03 GMT

Hi All,

I'm trying to use Intervalmatch to map a range of days to a specific date category value e.g. 0-94 days should be mapped to "<3 months" and 341-2000 days should be mapped to ">12 months". However, I'm getting very strange results, as you can see from the screenshot below... As you can see, I expect values of 198 days to map within "7-11 months", but I don't know why it's mapping "7-11 months" even for 563 days (should be mapped to ">12 months" instead). Also, 137 days should be mapped to "3-6 months" as well instead of "7-11 months".

Is there something I'm doing wrongly?? Thanks for all your help in advance.

[ABC]:
LOAD

Customer,

[Item],

[Date of Last Price],
Round(Now()-Date(Date#([Date of Last Price], 'D-MMM-YY'),'MM/DD/YYYY')) as [Days Last Changed]
FROM
test123.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[Day_to_Months]:

LOAD * INLINE

[
Start Days, Stop Days, Date Category, DayMonths Sort Order
0,94,< 3 months,4
95,186,3 - 6 months,3
187,340,7 - 11 months,2
341,2000,> 12 months,1
];

IntervalMatch([Days Last Changed])

LEFT JOIN ([ABC])

LOAD [Start Days],[Stop Days] Resident [Day_to_Months];

Re: Intervalmatch issue

Agis-Kalogiannis — Mon, 15 Sep 2014 09:04:14 GMT

Hi William

Is it possible to attach the test123.csv file you use in your script so as to have a sample of your data?

Thanks

Agis

Re: Intervalmatch issue

Agis-Kalogiannis — Mon, 15 Sep 2014 09:34:50 GMT

Ok... I thing you need to change the script to the following:

[ABC]:
LOAD

Customer,

[Item],

[Date of Last Price],
Round(Now()-Date(Date#([Date of Last Price], 'D-MMM-YY'),'MM/DD/YYYY')) as [Days Last Changed]
FROM
test123.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

[Day_to_Months]:

LOAD * INLINE

[
Start Days, Stop Days, Date Category, DayMonths Sort Order
0,94,< 3 months,4
95,186,3 - 6 months,3
187,340,7 - 11 months,2
341,2000,> 12 months,1
];

IntervalMatch([Days Last Changed])

// You need to use the Inline table that defines the intervals here and not the data table

LEFT JOIN ([Day_to_Months])

LOAD [Start Days],[Stop Days] Resident [Day_to_Months];

Could you please try this and let me know?

Ragards

Agis

Re: Intervalmatch issue

Anonymous — Thu, 09 Oct 2014 03:09:10 GMT

Hi,

Actually, the statement I use below still works.. I fixed where my bug lies...It's got NOTHING to do with Intervalmatch at all, but rather all the null rows generated because of unwanted associations and wrong JOINs . I reduced the various tables and fields down to a proper snowflake schema which was most suitable for Qlikview and everything worked like a charm!

IntervalMatch([Days Last Changed])

LEFT JOIN ([Temp Cost Price History])

LOAD [Start Days],[Stop Days] RESIDENT [Day_to_Months];

Thanks for your inputs!

Re: Intervalmatch issue

Agis-Kalogiannis — Thu, 09 Oct 2014 08:26:17 GMT

Thanks for your feedback William.

Maybe now that you have a correct data model, could consider to transform it towards a star schema, which, in general, is much more efficient for QlikView than a snowflake schema, in terms of performance.

Could you also please mark one of the answers above as correct or helpful?

Thanks

Agis