Scripting - nested if / and

[#1]

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!

[senpradip007]

Try like:

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

[tresesco]

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).

[#4]

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?