Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
beck110979
Valued Contributor III

FirstSortedValue and Inner Join

Hi Folks,

i got a question, i am using the firstsortedvalue () - function in script-area to show last value and it does work well

my table does look like:

id, name, city, id_date, amount

James, Munich, 09.09.2017, 3

John, Cologne, 12.03.2017, 23

but if i add an additional value with the same city, for instance: Cologne, instead of the last value: 25.12.2017 i got the following table:

id, name, city, id_date, amount

James, Munich, 09.09.2017, 3

John, Cologne, 12.03.2017, 23

John, Cologne, 25.12.2017, 12


my script -area does look like:


test1:

LOAD id,

    name,

    city,

    id_date,

    amount

FROM

ApplyMap.xlsx

(ooxml, embedded labels, table is FirstSortedValue);

Inner Join(test1)

load

id,

FirstSortedValue(distinct city, -id_date,1) as city

Resident test1

Group By id;



i want to achieve thies result:


id, name, city, id_date, amount

James, Munich, 09.09.2017,  3

John, Cologne, 25.12.2017, 12



Did i make any mistakes or am i missing something?


Thanks a lot for any feedback

Beck



8 Replies

Re: FirstSortedValue and Inner Join

Hi Beck, the Inner Join can be done using id and city, and keep the last date using Max(id_datedate):

Inner Join(test1)

load

id,

city,

Max(id_date) as id_date

Resident test1

Group By id, city;

beck110979
Valued Contributor III

Re: FirstSortedValue and Inner Join

Hi Martin,

thanks a lot for your response, it does work great, but i would like to know, how can i solve this issue with firstsortedvalue ()

Thanks a lot

Beck

mariusz_kumansk
New Contributor III

Re: FirstSortedValue and Inner Join

id is unique for this rows?
John, Cologne, 12.03.2017, 23

John, Cologne, 25.12.2017, 12

MVP
MVP

Re: FirstSortedValue and Inner Join

May be just this

test1:

LOAD id,

    name,

    city,

    Date(Max(id_date)) as id_date,

    FirstSortedValue(amount, -id_date) as amount

FROM ApplyMap.xlsx

(ooxml, embedded labels, table is FirstSortedValue)

Group By id, name, city;

jayanttibhe
Contributor II

Re: FirstSortedValue and Inner Join

Hi,

Do you really need FirstSorted value to achieve it? As I understand you want full row based on Max Dated per ID so inner join solution would be handy. If you really want

Also, I found some issue in your date format hence I modified its format in the original example.

You can try something like below if you wanted to go FirstSortedValue way.

Test1:

load

[id name],

FirstSortedValue(Distinct city, -id_date) as city

,FirstSortedValue(id_date, -id_date) as id_date

,FirstSortedValue(amount, -id_date) as amount

Group by [id name]

;

LOAD * INLINE [

    id name , city , id_date , amount

    James, Munich ,  09/09/2017, 3

    John , Cologne , 12/03/2017, 23

    John , Cologne , 25/12/2017, 12

];

Hope it helps!

Thanks

Jayant

beck110979
Valued Contributor III

Re: FirstSortedValue and Inner Join

Hi Mariusz,

yes it is, the id is unique for this rows

beck110979
Valued Contributor III

Re: FirstSortedValue and Inner Join

Hello Sunny,

Thanks a lot for your respone, i will implement it and report you whether i have any results

beck110979
Valued Contributor III

Re: FirstSortedValue and Inner Join

Hello Jayant,

Thanks a lot for your respone, i will implement it and report you whether i have any results

Community Browser