topic Re: Scripting - nested if / and in QlikView

Scripting - nested if / and

Thu, 21 Aug 2014 09:04:25 GMT

Good morning,

With HR data, you frequently have validity dates. I want to do a quality check identifying (a) records with overlapping validity dates or (b) gaps in the validity dates.

For this, I load, build a record key with several application-wise relevant fields, and order the data with that record key. Now I want to find overlapping validity dates (item (a) above). Technically, for a given record key, I want to check if a record´s start date falls within the start and end date of the previous record.

What is wrong with this script statement?

if (
      (peek( RecordKey, -1) = RecordKey)
       and (
      (peek (Beginndatum.BEGDA, -1) <= Beginndatum.BEGDA)
      and
      (Beginndatum.BEGDA <= peek (Endedatum.ENDDA, -1))),
DuplicateID1 = 'yes'

Many thanks in advance!

Re: Scripting - nested if / and

senpradip007 — Thu, 21 Aug 2014 09:11:53 GMT

Try like:

if (
      (peek( RecordKey, -1) = RecordKey)
       and (
      (peek (Beginndatum.BEGDA, -1) <= Beginndatum.BEGDA)
      and
      (Beginndatum.BEGDA <= peek (Endedatum.ENDDA, -1)), 'Yes') AS DuplicateID1

Re: Scripting - nested if / and

tresB — Thu, 21 Aug 2014 09:13:59 GMT

Try putting single quotes around field names in peek(), like:

peek( 'RecordKey', -1) // similarly for all

If that doesn't help, try to post your exact part of the script with load statement/variable declaration(as the section you posted seems with some errors).

Re: Scripting - nested if / and

Thu, 21 Aug 2014 09:57:03 GMT

Hi Pradip,

depending on the number of closing ")", I get either

"Error in expression: If takes 2-3 parameters" and in DuplicateID1 is not brown so it seems QlikView is not recognizing it as a field or

"Error in expression:')' expected" but DuplicateID1 is brown so it seems QlikView is recognizing it as a field

Any other suggestions?