Find most recent(max Date) records with group

[HAMID_AKBARI]

A table of:

wagon	wheel_no	Wheel_position	Date of Change	size
901	1b47	1	12/25/2022	356
901	3b63	2	12/25/2022	341
901	4b81	3	12/25/2022	365
901	6f47	4	12/25/2022	345
901	2b63	1	1/19/2023	346
901	7f31	2	1/19/2023	340
901	3b31	3	1/19/2023	350
901	1b80	4	1/19/2023	340
902	5f70	1	2/8/2023
902	3f45	2	2/8/2023
902	4b21	3	2/8/2023
902	9p31	4	2/8/2023

every wagon has 4 position that a wheel_no placed there after Maintenance.

I want this result by selecting a Wagon(Current Status of Wagon/Wheels):

Wagon Wheel Status
wagon	wheel_no	Wheel_position	Date of Change	Size
901	2b63	1	1/19/2023	346
901	7f31	2	1/19/2023	340
901	3b31	3	1/19/2023	350
901	1b80	4	1/19/2023	340

also need to four text boxes that contain wheel_no to demonstrate  graphical view of Wagon wheel status.

thanks genuis Qlik community inadvance

[JordyWegman]

Hi Hamid,

If limiting your data set is a possibility, use this code:

Source:
Load * Inline [
wagon	,	wheel_no	,	Wheel_position	,	Date of Change	,	size
901	,	1b47	,	1	,	25-12-2022	,	356
901	,	3b63	,	2	,	25-12-2022	,	341
901	,	4b81	,	3	,	25-12-2022	,	365
901	,	6f47	,	4	,	25-12-2022	,	345
901	,	2b63	,	1	,	19-1-2023	,	346
901	,	7f31	,	2	,	19-1-2023	,	340
901	,	3b31	,	3	,	19-1-2023	,	350
901	,	1b80	,	4	,	19-1-2023	,	340
902	,	5f70	,	1	,	8-2-2023	,	
902	,	3f45	,	2	,	8-2-2023	,	
902	,	4b21	,	3	,	8-2-2023	,	
902	,	9p31	,	4	,	8-2-2023	,	
]
;

// Get the unique combination
Table:
Load
	wagon & '|' & Max( [Date of Change] ) as Filter
Resident Source
Group by wagon
;

// NO conatenate is used because otherwise it will merge with the table Source
NoConcatenate
Final:
Load
	*
Resident Source
Where Exists( Filter, wagon & '|' & Floor( [Date of Change]  ) )
;

Drop tables Table,  Source;

Jordy

Climber

[HAMID_AKBARI]

Thanks Jordy

Sorry fo my English

I want the latest(Date of change) four wheel_no installed for each wagon i click by wheel_position,

as i search the net a combination of aggr and max functions might help but i dont know how

[HAMID_AKBARI]

I made the table in the SQL with max function and problem solved now i want to graphically show the Wheel_no in a text box here is  the problem:

assum that the filtered data is 

WAGON	AX	WHEEL_NO
W952	1	2B19
W952	2	1B29
W952	3	3B18
W952	4	3B59

how can i have this view:

need four text box contains wheel_no represent an ax,in other word convert the table into four seprate textbox

[sidhiq91]

@HAMID_AKBARI  Please see below the code used in the backend:

NoConcatenate
Temp1:
Load wagon,
wheel_no,
Wheel_position,
Date(Date#([Date of Change],'MM/DD/YYYY'),'MM/DD/YYYY') as Date,
size
inline [
wagon, wheel_no, Wheel_position, Date of Change, size
901, 1b47, 1, 12/25/2022, 356
901, 3b63, 2, 12/25/2022, 341
901, 4b81, 3, 12/25/2022, 365
901, 6f47, 4, 12/25/2022, 345
901, 2b63, 1, 1/19/2023, 346
901, 7f31, 2, 1/19/2023, 340
901, 3b31, 3, 1/19/2023, 350
901, 1b80, 4, 1/19/2023, 340
902, 5f70, 1, 2/8/2023,
902, 3f45, 2, 2/8/2023,
902, 4b21, 3, 2/8/2023,
902, 9p31, 4, 2/8/2023,
];

NoConcatenate
Temp2:
Load AutoNumber(wagon&Max_date) as HubKey,
wagon,
Max_date
;
Load wagon,
Date(Max(Date),'MM/DD/YYYY') as Max_date
Resident Temp1
group by
wagon;

NoConcatenate
Temp3:
Load
wagon as wagon1,
wheel_no,
Wheel_position,
Date,
AutoNumber(wagon&Date) as HubKey,
size
Resident Temp1;

inner join (Temp3)
Load HubKey
Resident Temp2;
Drop Table Temp1, Temp2;

Exit Script;

If this resolves your issue, please like and accept it as a solution.