Long nested if or other solution?

[#1]

Hi,

I have a data set of about 1M records  (40 months about 30K distinct IDs) that I need to group into 6 different groupings.

The data for the groupings is fixed but the source of course changes.

My reslut will be a table (Grouping) with unique IDs and all the 6 Groups that I use to easy make selections from the actual data.

I have done a rather long nested if like:

Grouping:

Load distinct UniqueID

If Type is A,

     if country is B

          if Brand is D, if Brand is C, if Brand is E, FixedValue) etc

) as Grouplevel1

Resident ItemDatabas

This works but the nested if's can be rather complex upto 150 rows and thus rather un maintenance friendly.

Could I store my fixed conditions in a table and join that in someway or other ideas?

Thanks

[simondachstr]

Have you thought of using ApplyMap() on a distinct key based on Type/Country/Brand. Alternatively, you can also contemplate left joining your "grouping table" based on those fields.

[ToniKautto]

I am very confident that you can find a better way. Some sample data is required in order to give any suggestion on approach.

[senpradip007]

Can you share your script?

[jonathandienst]

Assuming that the combination of Brand, Type and Country determine the group, you could create a spreadsheet/database table containing these 3 fields and the group name, and then choose one of:

• join this table to the main data table
• OR create a composite key in the main date table ad in this lookup data and use ApplyMap to add the group name
• OR create the composite keys in each table and just load the composite key and Group Name into a separate table and let QV take care of the associations.

Each option has its pros and cons. The composite keys would be a simple concatenation of the keys fields, or use autonumber on the key to make a numeric key in each table.

[#6]

Maintain all your conditions and result values into Excel file and use the ApplyMap to add the Group field to table.