Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Try like:
if (
(peek( RecordKey, -1) = RecordKey)
and (
(peek (Beginndatum.BEGDA, -1) <= Beginndatum.BEGDA)
and
(Beginndatum.BEGDA <= peek (Endedatum.ENDDA, -1)), 'Yes') AS DuplicateID1
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).
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?