12 Replies Latest reply: Apr 20, 2017 11:40 AM by Nishant Goel

# IntervalMatch on Resident Table

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:
DRIVER_AGE,
POLICY_NUMBER
From C:\QlikView\Daily Quote\v14\CombinedQuotes.qvd (qvd);

Carl

• ###### Re: IntervalMatch on Resident Table

Try this:

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

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

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

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

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

• ###### Re: IntervalMatch on Resident Table

Hi Carl,

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

• ###### Re: IntervalMatch on Resident Table

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

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

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

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

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

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.