Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
|
@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.