FirstSortedValue and Inner Join

[beck_bakytbek]

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

[rubenmarin]

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;

[beck_bakytbek]

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]

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

John, Cologne, 25.12.2017, 12

[sunny_talwar]

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]

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

[beck_bakytbek]

Hi Mariusz,

yes it is, the id is unique for this rows

[beck_bakytbek]

Hello Sunny,

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

[beck_bakytbek]

Hello Jayant,

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