topic Re: Concatenate and filter in QlikView

Concatenate and filter

Yintak — Tue, 14 Aug 2018 04:04:27 GMT

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

Re: Concatenate and filter

Nicole-Smith — Tue, 14 Aug 2018 16:11:23 GMT

You can use set analysis instead of if/match:

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

Re: Concatenate and filter

Yintak — Thu, 16 Aug 2018 09:32:55 GMT

Hi Nicole

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

Re: Concatenate and filter

tresB — Thu, 16 Aug 2018 09:37:22 GMT

Use wildcard '*' , like :

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

Re: Concatenate and filter

Anonymous — Thu, 16 Aug 2018 09:47:34 GMT

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

Re: Concatenate and filter

Yintak — Thu, 16 Aug 2018 09:47:57 GMT

Hi Tresesco

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

Re: Concatenate and filter

Yintak — Thu, 16 Aug 2018 09:52:06 GMT

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.

Re: Concatenate and filter

tresB — Thu, 16 Aug 2018 11:09:24 GMT

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

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

Re: Concatenate and filter

Yintak — Fri, 17 Aug 2018 02:31:48 GMT

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!

Re: Concatenate and filter

tresB — Fri, 17 Aug 2018 04:52:42 GMT

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.

Re: Concatenate and filter

Yintak — Fri, 17 Aug 2018 06:13:00 GMT

Not sure if I am doing anything wrong, but removing AGGR() gives me an invalid dimension.

Re: Concatenate and filter

tresB — Fri, 17 Aug 2018 06:17:42 GMT

You are probably using that expression as dimension. Try it otherwise, use [Invoice number] as dimension and the expression without aggr() as expression, then pull to exchange column positions of dimension and expression.

Re: Concatenate and filter

Yintak — Fri, 17 Aug 2018 06:51:21 GMT

Sorry, but please guide me through.

I have used [Invoice Number] as dimension.

Using the expression without AGGR() as expression? I am confused by this statement.

Thank you for your time to reply me.

Re: Concatenate and filter

tresB — Fri, 17 Aug 2018 06:54:33 GMT

PFA

Re: Concatenate and filter

Yintak — Fri, 17 Aug 2018 07:27:38 GMT

Thank you Tresesco.

I have tried your following method, it still returns me with status quo.

No worries, this is the closest I can get and will work with what I have.

I really appreciate your time and effort in helping me out (: