Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone!
I have a slight problem: I have table:
Alignment RUN ID:
ID MD RAT
001 1 1
001 2 1
001 3 1
001 4 1
002 1 2
002 2 2
003 3 3
003 1 3
so, I want to the table to look like below using WHERE EXIST in 'EDIT SCRIPT' to show the following:
ID MD RAT
003 1 3
002 2 2
003 3 3
001 4 1
So I want a table that show the updated value based on ID but if it is not update it should show the original value and in this case 001 has MD 4 and RAT 1 cause it has not been updated on Alignment Run ID table, also whenever there is a new ID for the same MD and RAT it should be automatically show the latest one based on ID no. which is increasing.
Thanks for any help.
Hi,
Assuming the ID is increasing , this code might fit your case:
Tmp:
LOAD * INLINE [
ID , MD , RAT
001 , 1 , 1
001 , 2 , 1
001 , 3 , 1
001 , 4 , 1
002 , 1 , 2
002 , 2 , 2
003 , 3 , 3
003 , 1 , 3
];
NoConcatenate
LOAD FirstSortedValue(RAT, -ID) AS RAT_Last_Update,
FirstSortedValue(ID, -ID) AS ID_Last_Update,
MD AS MD_Last_Update
Resident Tmp
Group by MD;
KR,
Michael
An approach
Tmp:
LOAD * INLINE [
ID , MD , RAT
001 , 1 , 1
001 , 2 , 1
001 , 3 , 1
001 , 4 , 1
002 , 1 , 2
002 , 2 , 2
003 , 3 , 3
003 , 1 , 3
];
NoConcatenate
LOAD
ID,
lastvalue(MD) as MD,
LastValue(RAT) as RAT
Resident Tmp
Group by ID;
drop Table Tmp;
Thanks Clever for fast respose but the row 003 is missing when I apply your solution.
Any suggestions??
How do you know weather ID has been updated or not on the AllignmentRunID tables I mean what basis?
As you can see MD is repeating that means the ID is updating new info for MD and the RAT is changed
Hi
what about this?
Tmp:
LOAD * INLINE [
ID, MD, RAT
001, 1, 1
001, 2, 1
001, 3, 1
001, 4, 1
002, 1, 2
002, 2, 2
003, 3, 3
003, 1, 3
];
Table:
NoConcatenate LOAD
ID, MD, RAT
Resident Tmp
where
MD<>Peek(MD)
order by MD, ID desc;
DROP Table Tmp;
As you mentioned data below 003 is not updated because its not repeated.
001 1 1
001 2 1
001 3 1
001 4 1
002 1 2
002 2 2
003 3 3
003 1 3
Please post some more sample data and what you are expecting?
Hi,
Assuming the ID is increasing , this code might fit your case:
Tmp:
LOAD * INLINE [
ID , MD , RAT
001 , 1 , 1
001 , 2 , 1
001 , 3 , 1
001 , 4 , 1
002 , 1 , 2
002 , 2 , 2
003 , 3 , 3
003 , 1 , 3
];
NoConcatenate
LOAD FirstSortedValue(RAT, -ID) AS RAT_Last_Update,
FirstSortedValue(ID, -ID) AS ID_Last_Update,
MD AS MD_Last_Update
Resident Tmp
Group by MD;
KR,
Michael
Weird, seems to appear here
Actually what I meant was Id 003 with MD 3 is missing but when I replaced MD with ID and ID with MD, the data is shown correctly. Sorry if I'm unclear with my question, but I really appreciate your help.