One Dimension with multiple conditions

[whiteside]

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 2  that somehow reflects the combination of 3 AND 4.

I hope you guys understand my needs.

best regards

whiteside

[marcel_olmo]

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.

[whiteside]

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:

[agigliotti]

i think you can use the below expression:

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

I hope it helps.

[robert99]

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

[whiteside]

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.

[whiteside]

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

[agigliotti]

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.

[marcel_olmo]

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 :

LOAD * INLINE [ 

    Shipment No, TextCodeKey

    ET150T686E027, T98

    ET150T686E027, T98

    ET150T686E027, T99

    ET150T686E028, T98

    ET150T686E028, T98

];

ExampleTable_Flag :

load

[Shipment No] as [Shipment No],

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

;

load

[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.

[robert99]

Hi Marcel

Could you do it outside script somehow using aggr

If (aggr (                                                        ,Textcode key))