Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
id is unique for this rows?
John, Cologne, 12.03.2017, 23
John, Cologne, 25.12.2017, 12
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;
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
Hi Mariusz,
yes it is, the id is unique for this rows
Hello Sunny,
Thanks a lot for your respone, i will implement it and report you whether i have any results
Hello Jayant,
Thanks a lot for your respone, i will implement it and report you whether i have any results