22 Replies Latest reply: Sep 12, 2018 12:00 PM by Marcel Olmo

# 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

• ###### Re: One Dimension with multiple conditions

Could you attach a sample of what you have and what you want to achieve?

Have you considered to do it via Script? Add a flag field which controls that kind of situations.

Regards, Marcel.

• ###### Re: One Dimension with multiple conditions

Hello Marcel,

you can see my screenshots right?

in short:

I have this:

sometimes it even could look like this:

And I want to achive this (just want a dimension that tells me if the combination of 2 or more attributes is fulfiled)

as mentioned before i tried something like this :IF((([Textcode key]='T99')  and ([Textcode key]='T98' )) ,'YES', 'NO')
but without success.

p.s. I have no Idea how to use the script. I am a Frontend User:

• ###### Re: One Dimension with multiple conditions

i think you can use the below expression:

if( count( distinct [Textcode key] ) > 1, 'YES', 'NO' )

I hope it helps.

• ###### Re: One Dimension with multiple conditions

Not Really.

Because I have Textcodes from T01 to T99.

So i could have this case for example (which would fulfill your condition and should say yes):

But I specificly want to too look for the combination of T98 AND T99.

Also at a later point I want to look for the Combination of T99 AND T98 AND TXX and so on and so on.

any idea?

p.s. BTW

I managed to create a Bar Chart using this formular:
Count(DISTINCT {(<[Textcode key] = {'T94'}> * <[Textcode key] = {'T99'}>)} [Shipment no])

But now I want this as a dimension so i can use it as a filter.

• ###### Re: One Dimension with multiple conditions

so you can use this one as dimension:

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

and untick show null values option.

• ###### Re: One Dimension with multiple conditions

The syntax looks nice:

but it doesnt seem to work

• ###### Re: One Dimension with multiple conditions

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] )

• ###### Re: One Dimension with multiple conditions

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] )

• ###### Re: One Dimension with multiple conditions

Hello Whiteside,

the point is as Robert says, T98 and T99 are in diferent rows, that's why I strongly suggest to do it via script.

Here I attach the code which solves what you want :

ExampleTable :

Shipment No, TextCodeKey

ET150T686E027, T98

ET150T686E027, T98

ET150T686E027, T99

ET150T686E028, T98

ET150T686E028, T98

];

ExampleTable_Flag :

[Shipment No] as [Shipment No],

if (TextCodeKey_Counter_98_99 =2,1,0) as TextCodeKey_Flag_98_99

;

[Shipment No] as [Shipment No],

count(DISTINCT TextCodeKey ) as TextCodeKey_Counter_98_99

Resident ExampleTable

Where

TextCodeKey = 'T98'

or

TextCodeKey = 'T99'

Group by [Shipment No]

;

I've created a new field in the script called TextCodeKey_Flag_98_99 which has two values : 1 if the shipment number has T98 and T99 as TextCodeKeys, and 0 if it hasn't both numbers.

Here's the final result :

Regards, Marcel.

• ###### Re: One Dimension with multiple conditions

Hi Marcel

Could you do it outside script somehow using aggr

If (aggr (                                                        ,Textcode key))

• ###### Re: One Dimension with multiple conditions

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?

• ###### Re: One Dimension with multiple conditions

Hello Whiteside,

there's no an obvious answer here. It depends on several things.

If you do it via App, it's more difficult to get, and you have to control a lot of things via set analysis and/or other syntax options.

If you do it via Script, it's more clean and you can get your new flag dimension available to be clicked in the app.

As you've seen in my example, it works well, and in that little portion of script you can add or delete easily more complex business rules.

Regards, Marcel.

• ###### Re: One Dimension with multiple conditions

Hi

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

Maybe because the 98 and 99 are on different rows

• ###### Re: One Dimension with multiple conditions

i didnt write "....always":
i wrote : --> doesn't work, always says no

• ###### Re: One Dimension with multiple conditions

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

• ###### Re: One Dimension with multiple conditions

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

• ###### Re: One Dimension with multiple conditions

maybe something like this could work

=IF(

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

+

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

=2,'Yes','No')

• ###### Re: One Dimension with multiple conditions

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 =)

• ###### Re: One Dimension with multiple conditions

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.

• ###### Re: One Dimension with multiple conditions

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