Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
BergsonF
Contributor II
Contributor II

Concatenate results from FirstSortedValue

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 🙂 

 

Labels (1)
1 Solution

Accepted Solutions
BergsonF
Contributor II
Contributor II
Author

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!

View solution in original post

7 Replies
BrunPierre
Specialist II
Specialist II

Try this.

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

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

Or
MVP
MVP

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

 

BergsonF
Contributor II
Contributor II
Author

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! 

BergsonF
Contributor II
Contributor II
Author

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 😞

BergsonF
Contributor II
Contributor II
Author

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

BergsonF
Contributor II
Contributor II
Author

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!