topic Re: Len() and WildMatch() in set analysis in QlikView

Len() and WildMatch() in set analysis

Cascader — Tue, 22 Mar 2022 13:19:10 GMT

I have the following expression,

len(trim(if(WildMatch(Queue ,'*'&Dept&'*'), Dept)))>0

I want to use inside a the following set analysis

aggr(ONLY({<Is_Dept_Group={'1'},Is_Case_Owner={'1'},_Closed={'0'}>}[Case Number]),[Case Number])

Re: Len() and WildMatch() in set analysis

rwunderlich — Tue, 22 Mar 2022 13:48:26 GMT

Add the set modifier:

[Case Number]={"=len(trim(if(WildMatch(Queue ,'*'&Dept&'*'), Dept)))>0"}

-Rob

Re: Len() and WildMatch() in set analysis

marcus_sommer — Tue, 22 Mar 2022 13:51:13 GMT

I think it's not possible within a classical set analysis because your condition requires a row-level evaluation. I think I would adjust a bit and using it as a boolean-condition within the aggr(), like:

aggr(
ONLY({<Is_Dept_Group={'1'},
Is_Case_Owner={'1'},
_Closed={'0'}>} [Case Number]) * sign(WildMatch(Queue ,'*'&Dept&'*'))
,[Case Number])

- Marcus

Re: Len() and WildMatch() in set analysis

Cascader — Tue, 22 Mar 2022 14:12:52 GMT

not worked, Rob!

Re: Len() and WildMatch() in set analysis

Cascader — Tue, 22 Mar 2022 14:13:30 GMT

not working either

Re: Len() and WildMatch() in set analysis

Cascader — Tue, 22 Mar 2022 14:35:44 GMT

I found this work around, not sure if it's best practice or not, however it give me the correct result

I used if to check the Lenght first and if it's > 0, then do the aggregation.

if(len(trim(if(WildMatch(Queue ,'*'&Dept&'*'), Dept)))>0, aggr(ONLY({<Is_Dept_Group={'1'},Is_Case_Owner={'1'},_Closed={'0'}>}[Case Number]),[Case Number]))