Which code is more efficient for binning ages? For the aficionado's........

[vkish16161]

Hi!

I want to bin age into the agegroup "0-20", "21-30" etc.. For that

1. Would a Nested IF statement be taking more toll on memory OR

2. This:

let Max_Age = 69;

For a = 1 to 20

A:

LOAD $(a) as AgeNumber, '0-20' as [Age Group]

AutoGenerate 1;

Next

For a = 21 to 30

Concatenate LOAD $(a) as AgeNumber, '21-30' as [Age Group]

AutoGenerate 1;

Next

For a = 31 to 40

concatenate LOAD $(a) as AgeNumber, '31-40' as [Age Group]

AutoGenerate 1;

Next

For a = 41 to 50

concatenate LOAD $(a) as AgeNumber, '41-50' as [Age Group]

AutoGenerate 1;

Next

For a = 51 to 60

concatenate LOAD $(a) as AgeNumber, '51-60' as [Age Group]

AutoGenerate 1;

Next

For a = 61 to $(Max_Age)

concatenate LOAD $(a) as AgeNumber, '60+' as [Age Group]

AutoGenerate 1;

Next

[oknotsen]

Scenario number 3:

Using IntervalMatch() function.

http://help.qlik.com/en-US/sense/3.0/Subsystems/Hub/Content/Scripting/ScriptPrefixes/IntervalMatch.h...‌

[#3]

Or a bit simpler

test:

For a = 0 to $(Max_Age)

LOAD $(a) as AgeNumber

AutoGenerate 1;

Next

Noconcatenate

test1:

LOAD *,

          IF(AgeNumber>=0 And AgeNumber<=20,Dual('0-20',1),

              IF(AgeNumber>20 And AgeNumber<=30,Dual('21-30',2),

                    etc...) As AgeGroup

Resident test;

Drop table test;

[marcus_sommer]

Here you will find various approaches how to solve such a task: Buckets. My order of solutions would be:

- class()

- mapping with applymap: Mapping … and not the geographical kind

- pick(match())

- nested-if

depending on the requirements of the whole application.

- Marcus

[vkish16161]

Nhu, I wanted to avoid Nested IF's because they take lot of toll on the memory.

But thanks for the help though.

[vkish16161]

Marcus,

I don't know how to use the class function for this particular scenario. 

Class function w/o IF's - Would be glad if you could provide an example)

Age      Age Group

0          0-20

1          0-20

57        50-60

68         60+

I have used mapping loads and solved this issue.

But I was wondering if the above script takes a lot of toll on the memory.

[vkish16161]

Excellent! Forgot about Interval Match until you reminded.

Thanks Onno

[oknotsen]

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .

[vkish16161]

Here's the simplified code using Interval Match.

Guys thanks for your help. Especially Onno.

A:

LOAD * INLINE [

    Age

    0

    11

    21

    20

    35

    69

    96

];

B:

left Join(A)

IntervalMatch(Age)

LOAD * INLINE [

    Age1, Age2

    0, 20

    21, 30

    31, 40

    41, 50

    51, 60

    61, 200

];

C:

LOAD Age, Replace(Age1&'-'&Age2,'61-200','>60' )as [Age Bucket]

Resident A

Order by Age;

DROP Table A;