topic Re: Re: Using Where Exists in QlikView

Using Where Exists

Fri, 03 Jan 2014 17:26:04 GMT

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.

Re: Using Where Exists

Clever_Anjos — Fri, 03 Jan 2014 17:40:33 GMT

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;

Re: Using Where Exists

Fri, 03 Jan 2014 18:42:46 GMT

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

Any suggestions??

Re: Using Where Exists

Fri, 03 Jan 2014 22:12:20 GMT

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

Re: Using Where Exists

Fri, 03 Jan 2014 22:17:58 GMT

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

Re: Using Where Exists

maxgro — Fri, 03 Jan 2014 22:47:42 GMT

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;

Re: Using Where Exists

Fri, 03 Jan 2014 22:56:26 GMT

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?

Re: Re: Using Where Exists

agilos_mla — Fri, 03 Jan 2014 23:04:44 GMT

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

Re: Using Where Exists

Clever_Anjos — Sun, 05 Jan 2014 12:30:09 GMT

Weird, seems to appear here

Re: Using Where Exists

Sun, 05 Jan 2014 14:17:11 GMT

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.