topic Re: Concatenate results from FirstSortedValue in QlikView

Concatenate results from FirstSortedValue

BergsonF — Wed, 23 Nov 2022 07:58:38 GMT

Hi,

I am having difficulties writing expressions where the answers need to concatenate results.

Here is a (small) dataset to illustrate :

[CATEGORY, LOCATION, DATE]

Car, Paris, 02/03/2022

Boat, Madrid, 08/03/2022

Car, Paris, 03/04/2022

Boat, London, 05/04/2022

Car, Barcelona, 07/04/2022

Plane, New York, 10/04/2022

Car, Barcelona, 12/05/2022

Car, Sydney, 12/05/2022

Here are the questions I need answered :

1 - Where was the last car sold ?

As you can see this brings two results. Barcelona and Sydney. Using the following doesn't work, since it can only return one answer :

FirstSortedValue({<[Category] = {'Car'} >} Location, - [Date])

I tried adding a concat() but get a "no nested expression" syntaxe error. I guess I should wrap it inside an Aggr() but can't figure out how.

- Where were the majority of the cars sold ?

Same thing here. Two results should be returned, Paris and Barcelona. I've tried a few expressions but can't find a suitable, working one, that concatenates the results if there are more than one (brace yourselves, you will probably find this very poorly written) :

FirstSortedValue(Aggr(Concat(distinct [Location], ','), [Location], [Category]), Aggr(Sum([Category] = 'Car'), [Location], [Category]))

Any help to figure out how these should be written would be greatly appreciated!

Thanks 🙂

Re: Concatenate results from FirstSortedValue

BrunPierre — Wed, 23 Nov 2022 10:30:44 GMT

Try this.

=Concat(Aggr(FirstSortedValue({<CATEGORY={'Car'}>}LOCATION,-DATE),LOCATION),', ')

Re: Concatenate results from FirstSortedValue

BergsonF — Wed, 23 Nov 2022 10:54:50 GMT

Just tried it and it does concatenate, but all the locations. It does not provide only the 'latest' event, the FirstSortedValue does not seem to come into play...

Tried :

FirstSortedValue(Aggr({<CATEGORY = {'Car'} >} Concat(distinct LOCATION, ','), LOCATION), -DATE)

Syntax is OK, but no result. 😞

Re: Concatenate results from FirstSortedValue

Or — Wed, 23 Nov 2022 10:45:18 GMT

Have a look at this solution, which you should be able to adapt to your scenario:

https://community.qlik.com/t5/New-to-Qlik-Sense/Concatenate-Results-if-FirstSortedValue-is-a-Tie/m-p/1330420

Re: Concatenate results from FirstSortedValue

BergsonF — Thu, 24 Nov 2022 09:54:52 GMT

Edit : the following works and answers my second question "Which is the top city for Car sales?"

Concat(distinct Aggr(if(Count({<[CATEGORY] ={'Car'} >} [LOCATION]) = Max(TOTAL Aggr(Count({<[CATEGORY] = {'Car'} >} [LOCATION]), [LOCATION])), [LOCATION]), [LOCATION]), ',')

I will try to start from there to find a way to answer my first question :" Where were the last cars sold?"

Thanks Or, for pointing me in the right direction!

Re: Concatenate results from FirstSortedValue

BergsonF — Thu, 24 Nov 2022 10:53:41 GMT

Returning to this...

FirstSortedValue({<[CATEGORY ] = {'Car'} >} LOCATION , - [DATE])

^^ This expression works properly, but only if there is one result to return.

Concat(FirstSortedValue({<[CATEGORY] = {'Car'} >} LOCATION, - [DATE]), ',')

^^ Does not work because of the "no nested expression" error

Concat(Aggr(FirstSortedValue({<[CATEGORY ] = {'Car'} >} LOCATION, - [DATE), LOCATION), ',')

^^ Seems to work but returns all the locations of my dataset.

Qliksense language is really obscure for me, but if I had to perform this operation in python or Java, I would first find the latest entries (FirstSortedValue for 'Car'), and then, IF there are several possible results, concatenate them.

So I tried going the other way around with :

FirstSortedValue({<[CATEGORY]={'Car'}>} Aggr(Concat(distinct LOCATION, ','), LOCATION), -DATE)

... And got nothing.

Starting to get frustrated at this point 😞

Re: Concatenate results from FirstSortedValue

BergsonF — Thu, 24 Nov 2022 15:28:44 GMT

Trying to "think out of the box", I tried an other method :

1. Find the last date for any car sale

2. Find and concatenate sales matching that date.

I started with creating a measure called 'LastDateCar' :

Date(FirstSortedValue(distinct {<[CATEGORY] = {'Car'} >} DATE, -DATE), 'DD/MM/YYYY')

The result is '12/05/2022'. So far so good.

Then I wrote this short expression, just to check if I was able to concatenate the locations properly :

Concat(distinct Aggr(if(DATE = '12/05/2022', LOCATION, 'Error'), LOCATION), ',')

I got the following return 'Sydney, Barcelona'. Great.

However, when I tried to use my custom measure 'LastDateCar' :

Concat(distinct Aggr(if(DATE = LastDateCar , LOCATION, 'Error'), LOCATION), ',')

I get all the locations where cars were sold, along with 'Error'...

Sigh...

Re: Concatenate results from FirstSortedValue

BergsonF — Fri, 25 Nov 2022 10:12:27 GMT

I found the solution :

1. Where was the last car sold ?
In case there is more than one result, here is how I did it.
Created a 'variable' (not a 'measure') called 'LastCarSold' :
Date(FirstSortedValue (distinct {<[CATEGORY] = {'Car'} >} DATE, -DATE), 'DD/MM/YYYY')

And then called this variable in the expression :
Concat(distinct {<[CATEGORY] = 'Car' > + <DATE = {'$(=$(LastCarSold))'} >} LOCATION, ',')

I first get the last date for a specific event, and I concatenate the locations for these specific events that happened at the same date. Not optimal, but it works.

2. Where were most cars sold ?
I used the expression from the link provided earlier.
Concat(Distinct Aggr(if(Count({<[CATEGORY] ={'Car'} >} LOCATION) = Max(TOTAL Aggr(Count({<[CATEGORY] ={'Car'} >} LOCATION), LOCATION)), LOCATION), LOCATION), ',')

Thanks for your help guys!