topic Re: IntervalMatch on Resident Table in QlikView

IntervalMatch on Resident Table

carlcimino — Wed, 19 Apr 2017 20:35:32 GMT

Hello, I am trying to bucket uneven age groups on a resident table. I'd like the buckets to be as follows. I'd like the age group be appended to the resident table. How would I accomplish that?

0 to 14

15-19

20-24

25-29

30-34

35-39

40-44

45-49

50-54

55+

Here's my resident table.

Quotes:
Load
DRIVER_AGE,
POLICY_NUMBER
From C:\QlikView\Daily Quote\v14\CombinedQuotes.qvd (qvd);

Thanks in advance!

Carl

Re: IntervalMatch on Resident Table

Anonymous — Wed, 19 Apr 2017 20:45:41 GMT

Try this:

Load
DRIVER_AGE,
POLICY_NUMBER,

IF(DRIVER_AGE<15,'0 -14',

IF(DRIVER_AGE<20,'15 -19',

IF(DRIVER_AGE<25,'20 -24',

IF(DRIVER_AGE<30,'25 -29',

IF(DRIVER_AGE< 35,'30 -34',

IF(DRIVER_AGE< 40,'35 -39',

IF(DRIVER_AGE< 45,'40 -44',

IF(DRIVER_AGE< 50,'45 -49',

IF(DRIVER_AGE< 55,'50 -54','55+'))))))))) as [Age Group]

From C:\QlikView\Daily Quote\v14\CombinedQuotes.qvd (qvd);

Re: IntervalMatch on Resident Table

Wed, 19 Apr 2017 21:21:40 GMT

Hello Carl,

Using interval match, example qvw attached.

EDIT: Age bucket table replaced with Age Group list box

Warm regards,

Nish

Re: IntervalMatch on Resident Table

Anonymous — Wed, 19 Apr 2017 21:27:57 GMT

Did you look at the new field [Age Group]?

Re: IntervalMatch on Resident Table

Thu, 20 Apr 2017 11:58:07 GMT

Hi Carl,

Have you had a chance to look at the replies from Caroline and myself yet?

Re: IntervalMatch on Resident Table

carlcimino — Thu, 20 Apr 2017 13:11:28 GMT

Hi Caroline, Thanks for replying. I was hoping to avoid the nested if statements. That is how I have it now.

Thanks again!

Carl

Re: IntervalMatch on Resident Table

carlcimino — Thu, 20 Apr 2017 13:13:37 GMT

Hi Nishant, Thank you for replying as well. I was looking to add the field to the resident table not add a dimension table without using nested if statements. Does that make sense?

Thanks,

Carl

Re: IntervalMatch on Resident Table

carlcimino — Thu, 20 Apr 2017 13:29:24 GMT

Also, is there any performance benefit to having the buckets as a dimension table instead of appending the field to your fact table? If not then I will use your method.

Thanks,

Carl

Re: IntervalMatch on Resident Table

carlcimino — Thu, 20 Apr 2017 13:31:59 GMT

Sorry one more question... I see you have start of 0 and end of 100 in your inline table. How would you account for positive and negative infinity?

Re: IntervalMatch on Resident Table

Thu, 20 Apr 2017 14:55:49 GMT

Hi Carl,

In QlikView a single table approach can be bad for RAM consumption and Script run time as far as I know, there are many pros for having the buckets as a dimension table instead. This approach offers flexibility too when dealing/administering future changes to the age group as you would just need to amend the inline load script below without touching your fact table. I hope this makes sense.

Warm regards,

Nish

Re: IntervalMatch on Resident Table

Thu, 20 Apr 2017 14:57:57 GMT

I'm sorry i don't understand. Perhaps post it under a new thread and I will take a look for you.

Re: IntervalMatch on Resident Table

carlcimino — Thu, 20 Apr 2017 15:08:19 GMT

Your buckets. 0 and 100. If my fact table has negative, or null values or numbers higher than 100 what happens? How would you account for those in your inline load?

Re: IntervalMatch on Resident Table

Thu, 20 Apr 2017 15:40:38 GMT

The data in my inline load is the age groups you provided on your original question and assumes the DRIVER_AGE in your resident load has positive values (source database shouldn't allow negative values here!)

Bottom line - i'm no expert but if you prepare the inline load according to the data in your fact table, it should do the trick for you.