topic Re: Where All Previous Records in QlikView

Where All Previous Records

Anonymous — Wed, 29 Nov 2017 15:08:31 GMT

Hi,

I have a question.

After running a script, I get the following table:

What I want to do, is a WHERE restriction in the script, that won't upload the BLUE highlighted column, since the record of the field STATE_AACL_TO_MATCH it has already been uploaded ('WIELKOPOLSKIE'). So I was thinking on something like:

WHERE STATE_AACL_TO_MATCH <> Previous(STATE_AACL_TO_MATCH), but that Previous has to consider all the previous values.

Does it exist something like this?

I tested something with Where Not Exists (STATE_AACL_TO_MATCH,STATE_AACL_TO_MATCH) but it didn't work as well...

Thank you very much and best regards to all.

Re: Where All Previous Records

el_aprendiz111 — Wed, 29 Nov 2017 15:47:48 GMT

Hi,

WHERE STATE_AACL_TO_MATCH <> Previous(STATE_AACL_TO_MATCH

ORDER BY STATE_AACL_TO_MATCH

Re: Where All Previous Records

sunny_talwar — Wed, 29 Nov 2017 15:56:54 GMT

How about this

Where Not Exists (STATE_AACL_TO_MATCH)

Re: Where All Previous Records

Anonymous — Fri, 01 Dec 2017 09:17:32 GMT

That seems good. The only thing missing (that I forgot to clarify) is that the STATE_AACL_TO_MATCH that I want to preserve is the one with highest Similarity value.

So in that case I could do like...

WHERE STATE_AACL_TO_MATCH <> Previous(STATE_AACL_TO_MATCH)

ORDER BY Similarity DESC, STATE_AACL_TO_MATCH;

Something like that I guess.

Thanks!

Re: Where All Previous Records

Anonymous — Fri, 01 Dec 2017 09:18:20 GMT

Sorry, it would be:

ORDER BY STATE_AACL_TO_MATCH, Similarity DESC

Re: Where All Previous Records

johnca — Fri, 01 Dec 2017 21:26:03 GMT

Maybe this...

RESULT:

Load

STATE_AACL_TO_MATCH

FirstValue(STATE_OAG) as FIRST_OAG,

FirstValue(SIMILARITY) as FIRST_SIMILARITY

Group By STATE_AACL_TO_MATCH

From DATA

(Or Resident TABLE)