Problem with firstsortedvalue

[jlk]

Hi, I have a problem with firstsortedvalue formula.

I have this kind of data:

NR	Date	C	D	E
123456	02.01.2011	abc	q	1
123456	01.02.2022	abc	q	2
987654	03.03.2012	def	q	3
324523	02.04.2020	ghi	q	4
234567	03.05.2018	xyz	q	5
234567	02.01.2011	xyz	q	6
234567	01.02.2022	xxx	q	7
234567	03.03.2012	xyz	q	8

I want to select unique value from the first column (NR), but it has to be sorted by Date column from the latest date. In addition, I need to add columns C, D and E (no matter what is there).

I expect such a result

NR	Date	C	D	E
123456	01.02.2022	abc	q	2
987654	03.03.2012	def	q	3
324523	02.04.2020	ghi	q	4
234567	01.02.2022	xxx	q	7

[sidhiq91]

@jlk  Please see below that I have created in Script. Please like and accept as resolution if it as solved your issue.

NoConcatenate
Temp:
Load *,
AutoNumber(Date&''&NR) as ID;

Load NR,
Date(Date#(Date,'DD.MM.YYYY'),'MM/DD/YYYY') as Date,
C,D,E;

Load * Inline [
NR, Date, C, D, E
123456, 02.01.2011, abc,q, 1
123456, 01.02.2022, abc,q, 2
987654, 03.03.2012, def,q, 3
324523, 02.04.2020, ghi,q, 4
234567, 03.05.2018, xyz,q, 5
234567, 02.01.2011, xyz,q, 6
234567, 01.02.2022, xxx,q, 7
234567, 03.03.2012, xyz,q, 8
];

Inner join(Temp)
Temp1:
Load *,AutoNumber(Max_date&''&NR) as ID;

Load Date(Max(Date),'MM/DD/YYYY') as Max_date,
NR

Resident Temp
group by NR;

Drop Fields ID, Max_date;

//Drop table Temp;
Exit Script;

[jlk]

Thank you very much for your explanations. Unfortunately I haven't been able to implement the solution yet because the whole table connects and gets data directly from SQL. I am trying to figure out the order in which it should be.
 

[sidhiq91]

@jlk  This solution right here should be an easy implementation for you to get the desired output once you get the entire data from SQL. Let me know if you are facing any issues, I can try to help you out.

[jlk]

Really sorry for my dilemmas but i'm a beginner... Unfortunately it does not work.

I'm not sure of the order. The first sholud be sql script?

SELECT

"NR",

"Date",

"C",

"D",

"E";

from xxxxxxxxxx ?

and then load...and your proposition?do I think wrong? You may need to use preceding load here?

The second question is about this statment "Load * Inline [
NR, Date, C, D, E
123456, 02.01.2011, abc,q, 1
123456, 01.02.2022, abc,q, 2
987654, 03.03.2012, def,q, 3
324523, 02.04.2020, ghi,q, 4
234567, 03.05.2018, xyz,q, 5
234567, 02.01.2011, xyz,q, 6
234567, 01.02.2022, xxx,q, 7
234567, 03.03.2012, xyz,q, 8
];"

I have (in sql) table, which is large. What is above it was supposed to be just an example and now i don't know how to apply it.

I try in different ways, but I still have the message that the ""Temp" table was not found"