
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Concatenate and filter
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
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I should have read your requirement more carefully. Try like:
Aggr(Concat( DISTINCT {<[Invoice number]=e({<[Status Code]={'ABC'}>})>} [Status Code], ',') ,[Invoice number])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can use set analysis instead of if/match:
=AGGR(Concat({<[Status Code]-={'ABC'}>}distinct [Status Code],' , '), [Invoice Number])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Nicole
Thank you for your input, however I still get back the same result as above.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Use wildcard '*' , like :
=AGGR(Concat({<[Status Code]-={'*ABC*'}>}distinct [Status Code],' , '), [Invoice Number])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Tresesco
Adding a wildcard does not give me the intended result, thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I should have read your requirement more carefully. Try like:
Aggr(Concat( DISTINCT {<[Invoice number]=e({<[Status Code]={'ABC'}>})>} [Status Code], ',') ,[Invoice number])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- « Previous Replies
-
- 1
- 2
- Next Replies »