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:
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
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?
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.
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:
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.