*** IntervalMatch on String Field (Age Range) *** Please help!!!

[IAMDV]

Dear All,

I am stuck with a task. I have a dataset which is 3 million+ records and I had loaded the data from QVD. Now, I need to convert a free value Age into intervals/range. I had used intervalmatch in the past with numbers but now I need to use string values. My idea is to use the RecNo() function and generate autonumbers for each row and then using applymap function I wanted to call the numeric values of Age string Field. I am not sure if this is right appraoch but I am stuck now... I need urgent help on this one.

I had attached the excel workbook which contains the Age String Field as sample data.

Many thanks in advance!

Cheers - DV

[#2]

DV, Check the attached application. Can some thing like this helps you?

- Sridhar

[IAMDV]

Mate - This works like a charm. Good thinking! However, please can you mock up an example if the data does not have any numeric value. For example I had attached some data on ethnic groups. Please can you guide me the solution for that scenario?

Also one more question about hierarchies. I mean if I wanted to show the Age Range from previous example and also a list of actual age number when I expand the group. What is the best way to achieve this one?

Thanks a ton again for your help! much appreciated

Cheers - DV

[IAMDV]

Mate - Just to update you. There are more string fields like ethnic group and I had to use intervalmatch for them to group them together. Please do let me know, what is the best appraoch to solve the issue. Also can you answer the hierarchy question as well?

Thanks again for your time.

Cheers - DV

[#5]

I would think for the ethnic groups the easiest approach would be to use a mapping load & applymap to identify your generic ethnicity.

ethnic_map:

Mapping load ethnicity, GenericEthnicity from Ethnic_Group.xls;

source_table:

load applymap('ethnic_map',Detailed_EthnicGroup) as Generic_Ethnicity,

*;

load * from source_data.xls;