Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Using Where Exists

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.

Tags (2)
1 Solution

Accepted Solutions
agilos_mla
Contributor III

Re: Re: Using Where Exists

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

9 Replies
Employee
Employee

Re: Using Where Exists

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;

Not applicable

Re: Using Where Exists

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

Any suggestions??

Not applicable

Re: Using Where Exists

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

Not applicable

Re: Using Where Exists

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

MVP
MVP

Re: Using Where Exists

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;

Not applicable

Re: Using Where Exists

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
Contributor III

Re: Re: Using Where Exists

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

Employee
Employee

Re: Using Where Exists

Weird, seems to appear here

Capturar.PNG.png

Not applicable

Re: Using Where Exists

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.

Community Browser