Concatenate and filter

[Yintak]

Good day

Please help me with this question, I have a a table of status codes and invoice numbers; see example:

By using the concatenate expression, =AGGR(Concat(Distinct[Status Code], ', '), [Invoice Number])

I will get back something like this:

I then use: =AGGR(Concat(distinct If(Not Match(Status Code, 'ABC'), Status Code),' , '), [Invoice Number])

I get this:

I want to remove all invoice numbers that contain the status code "ABC", how should I go about doing this?

Am I moving in the correct direction? Thank you in advance!

Best regards

Yin Tak

[Nicole-Smith]

You can use set analysis instead of if/match:

=AGGR(Concat({<[Status Code]-={'ABC'}>}distinct [Status Code],' , '), [Invoice Number])

[Yintak]

Hi Nicole

Thank you for your input, however I still get back the same result as above.

[tresesco]

Use wildcard '*' , like :

=AGGR(Concat({<[Status Code]-={'*ABC*'}>}distinct [Status Code],' , '), [Invoice Number])

[Anonymous]

try this as calculated Dimension

=if (match(AGGR(Concat(distinct [Status Code],' , '), [Invoice Number]),'ABC')=0,
AGGR(Concat({<[Status Code]-={'ABC'}>}distinct [Status Code],' , '), [Invoice Number]))

[Yintak]

Hi Tresesco

Adding a wildcard does not give me the intended result, thanks!

[Yintak]

Hi Rudolf

Thank you for your input as well, unfortunately this is my end result:

As a last resort, I will go to the back-end to modify the script data.

[tresesco · Accepted solution]

I should have read your requirement more carefully. Try like:

Aggr(Concat( DISTINCT {<[Invoice number]=e({<[Status Code]={'ABC'}>})>} [Status Code], ',') ,[Invoice number])

[Yintak]

Hi Tresesco

Your formula works as intended

Just wanted to point out that I could not replicate this with my data sets as I think it is too huge. Qliksense still returned with the Invoice Numbers, but the corresponding fields are empty. That is when I know the Invoice contains Status Code 'ABC'.

Thank you everyone for your time, and have a great day!

[tresesco]

Actually you don't need aggr() in the expression. Use [Invoice number] as dimension and then the expression (without aggr), like:

Concat( DISTINCT {<[Invoice number]=e({<[Status Code]={'ABC'}>})>} [Status Code], ',')

If the column order is a factor, you can always adjust that by pulling across.

This way your expression would lighter and hopefully you would be able to use the same in your big data set as well.