Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
whiteside
Contributor III
Contributor III

One Dimension with multiple conditions

Hello Together,

I am at a Dead End.

Please refer to the pic below.

I have one Element (in this case Shipment No) with Multiple "textcode keys".  You can see that by the Sum I do there (for columns right after the shipment no).

I have troubles to identify if one has more then one. You see in the picutre below i use in row

3 = IF((([Textcode key]='T98' )) ,'YES', 'NO')  --> And it Works

4 = = IF((([Textcode key]='T99' )) ,'YES', 'NO')  --> And it works

so now I tried in 1 and 2 to combine them without success.

First I tried 2 = IF((([Textcode key]='T99')  and ([Textcode key]='T98' )) ,'YES', 'NO')  --> doesn't work, always says no

and then 1 = MaxString( If(([Textcode key] =('T98') AND [Textcode key] =('T99'))  ,Dual('YES', 2),   Dual('no', 1)))

--> doesn't work, always says no

I expected to get "YES" in 1 or 2 but I always get "no".

Ideally I would delete 1 till 4 and have just one dimension that says yes if one or more Textcodes are found. All I need is some variaton of formular 1 or that somehow reflects the combination of 3 AND 4.

I hope you guys understand my needs.

best regards

whiteside

22 Replies
robert99
Specialist III
Specialist III

That's because the T98 and T99 are on different rows. So using 'and' gives a result of No

whiteside
Contributor III
Contributor III
Author

Hello Marcel,

Thank you. This might be an option. yet again this is only for the script.

I just need to know if this Script fix really is the only option? Is there no way this can be done in the App self?

I had a similar problem before and stalwar1  was able to fix it in the App. https://community.qlik.com/thread/292083 --> The only difference (as far as I can tell) here is that I need now a AND syntax and not an OR.

Or am I wrong and with the way qlik works, it is not possible in the App?

whiteside
Contributor III
Contributor III
Author

The syntax looks nice:

but it doesnt seem to work

robert99
Specialist III
Specialist III

Try using as a dimension

aggr(concat( Textcode key],'/') , [Shipment Num])

with another dimension as a [Shipment Num]

if this shows both T98 and T99

You then might be able to use 'wildmatch' and 'if' to get the result you want

agigliotti
Partner - Master III
Partner - Master III

try with the below calculated dimension:

Aggr( if( Count( DISTINCT {< [Textcode key] = {'T98'} >} [Shipment no] ) >= 1 and Count( DISTINCT {< [Textcode key] = {'T99'} >} [Shipment no] ) >= 1, [Shipment no] ), [Shipment no] )

robert99
Specialist III
Specialist III

maybe something like this could work

=IF(

WILDmatch(  (aggr(concat(Code,',') , SHipNum)), '*T99*')

+

WILDmatch(  (aggr(concat(Code,',') , SHipNum)), '*T98*')

=2,'Yes','No')

whiteside
Contributor III
Contributor III
Author

intriguing out of the box thinking 'I tried your contact and here is a quick example of what it looks now:

In the Dimension after [Shipment no] I have

=aggr(concat( [Textcode key],'/') , [Shipment no])

and in the Dimension after that i put:

=WildMatch ((aggr(concat( [Textcode key],'/') , [Shipment no])),'*T98*','*T99*')


In this example you have 4 different types with 3 different outcomes.

I simply need to identify the one marked here yellow.

I am not sure if i use the Wildmatch correct or if i need to try something else.

whiteside
Contributor III
Contributor III
Author

ok, i could use this (see last column in screenhot)

=WildMatch ((aggr(concat( [Textcode key],'/') , [Shipment no])), '*T98/T99*') And use the IF clause looking for the 1.

It seems that Aggr(Concat) sort them Alphabetically so in this case where I look for the combination T98 and T99 it works. But if I needed to look for the combination T33 and T99 and in the dataset there would be a T50  aswell then the Aggr(Concat) would probably say T33/T50/T99 thus rendering the syntax
(=WildMatch ((aggr(concat( [Textcode key],'/') , [Shipment no])), '*T33/T99*') useless

whiteside
Contributor III
Contributor III
Author

True 😃 just sum them up and check for two.

It been an intersting Journey to get here but I think I can live with that.

Thanks to all that helped getting here. It's been like always, fun and successful 😃

whiteside
Contributor III
Contributor III
Author

This one Works 😃
i just did a small change to fit my needs but this is what I needed.

=Aggr( if( Count( DISTINCT {< [Textcode key] = {'T98'} >} [Shipment no] ) >= 1 and Count( DISTINCT {< [Textcode key] = {'T99'} >} [Shipment no] ) >= 1, 'YES','NO'), [Shipment no] )