Using Where Exists

[#1]

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.

[Clever_Anjos]

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;

[#3]

Thanks Clever for fast respose but the row 003 is missing when I apply your solution.

Any suggestions??

[#4]

How do you know weather ID has been updated or not on the AllignmentRunID tables I mean what basis?

[#5]

As you can see MD is repeating that means the ID is updating new info for MD and the RAT is changed

[maxgro]

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;

[#7]

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?

[agilos_mla · Accepted solution]

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

[Clever_Anjos]

Weird, seems to appear here

[#10]

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.