Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

select values of listbox according to sql-similar condition in macro

Hi,

since a customer wants to combine the results of different selections, I am creating a macro to do this. the results of the selections are always a number of IDs, and they are supposed to be combined in different combinations (AND, OR, brackets, etc.). So I combined them to a sql-similar condition like this:

ID in (0002572330,0003586436,0010221679) AND  NOT  (ID in (0001224962,0003167139,0010149590) OR ID in (0003614268,0001266346,0000956499) )

(This is just an example, there can be much more IDs in each part. The maximum count of possible IDs is about 1.5 Mio.

My problem is now the selection... I mean reducing the field (listbox?) containing the possible IDs to the ones in the statement. Do you know a way for that? (a way I tried was exporting all of the IDs to a textfile with this one column and using this textfile as a database where I execute a select statement containing the clause above, but this is slow and is very client dependent (32bit vs. 64bit and so on).

Any ideas would be highly appreciated! :-)

Thanks a lot,

Martin

3 Replies
mov
Esteemed Contributor III

Re: select values of listbox according to sql-similar condition in macro

Hint - instead of macro, think of a set analysis expression (in your chart or a calculated list box).

Not applicable

Re: select values of listbox according to sql-similar condition in macro

Hi,

could a calculated listbox be based on such a clause?

Martin

mov
Esteemed Contributor III

Re: select values of listbox according to sql-similar condition in macro

Martin,

You can do a lot of tricks using expressions with set analysis.  In your case, the expression is, I assume, this:
=aggr(only({<ID={0002572330,0003586436,0010221679}> - (<ID={0001224962,0003167139,0010149590}> + <ID={0003614268,0001266346,0000956499}>)} ID) , ID)

See attached.

Apparently it makes more sense when the comma-separated lists of the hardcoded values are replaced with expressions or variables that create these lists.

Regards,
Michael

Community Browser