Create field from list of values

[#1]

I am VERY new to qlikview. I need to create a field that counts up the names where the value in field Dx equals a long list of values. 1.0-1.9, 2.0-2.9, 5.9, 8.1-8.3, etc. I tried using an expression where I typed in the definition box "Dx=1.1 or Dx=1.2 or Dx=1.3 or Dx=1.4" and so on. After so many values, Qlikview literally just shuts down. How can I get around this?

[johnw · Accepted solution]

No idea why QlikView would crash on a long expression, but there are better approaches, yes. If you want to stick with just an expression change, set analysis would probably be the most efficient way to process the list:

count({<Dx={1.1,1.2,1.3...etc...2.8,2.9,5.9,8.1,8.2,8.3}>} Name)

However, it might be better to create a separate field based on the list. That would move most of the processing time to the load script, which is much better, at least from the perspective of a user waiting for the chart to respond. Here's one way:

[Some Table]:
LOAD ...
,Dx
,Name
,if(match(Dx,1.1,1.2,1.3...etc...2.8,2.9,5.9,8.1,8.2,8.3),Name) as "Special Name"
...
FROM wherever
;

Here's another. It might look more complicated, but the advantage here is that you could easily replace the inline load with a load from a spreadsheet, a database, or however else you might want to identify the special Dx values without needing to edit your load script every time you want to change the list:

[Special Dx Values]:
LOAD * INLINE [
Special Dx Value
1.1
1.2
...
2.9
5.9
8.1
8.2
8.2
];

[Some Table]:
LOAD ...
,Dx
,Name
,if(exists("Special Dx Value",Dx),Name) as "Special Name"
...
FROM wherever
;

DROP TABLE [Special Dx Values];

With either load script solution, you then just count special names instead of names, and no longer need any special logic in the expression, which should make it execute very quickly:

count("Special Name")

[#3]

This worked great. Used it in various ways for several different functions. Thanks!