Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Long nested if or other solution?

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

5 Replies
simondachstr
Valued Contributor III

Re: Long nested if or other solution?


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.

Employee
Employee

Re: Long nested if or other solution?

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
Valued Contributor III

Re: Long nested if or other solution?

Can you share your script?

MVP
MVP

Re: Long nested if or other solution?

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.

Not applicable

Re: Long nested if or other solution?

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