gettings date from record above

[amien]

i have this table:

unique key, firstname, lastname, birthdate, FileNumber

21, M. , Carlo, 19700101, 1

21, M. , Carlo, 0 , 2

21, M.J. Carlo, 19850101, 3

21, M.J, Carlo, 0 , 4

21, W.J. Carlo, 19890101, 5

21, W.J, Carlo, 0 , 6

21, W.J, Carlo, 19990101 , 7

In an additional expression, i want for record 2 and 4 a birthday .. based on the 100% match of the firstname, lastname and unique key.

Record 6 shouldn't have a birthdate, because there is a conflict (more then one result)

[Oleg_Troyansky]

Getting a date from the record above is pretty simple - you need to compare your three keys and if they match, use peek(Date). Something like the following:

load
 Key,
 FirstName,
 LastName,
 if(previous(Key) =Key and previous(FirstName) = FirstName and previous(LastName)=LastName and Date=0, peek(Date), Date) as Date
from ...
order by
 Key, FirstName, LastName, Date

As far as your other condition (two different dates) - you'll have to come up with some data validation logic. If you can't validate your data prior to loading it into QlikView, you can aggregate the table and find out all the people that have more than one non-zero Date, and then flag them one way or another...

cheers,

[amien]

Thanks for your reply .. i know the previous and peek option .. i has really asking for an expression solution not a load solution. If there is no other way, then i could use the previous/peek location,

if i have two different dates. then it shouldnt replace the 0 birthdate with the peek(birthdate).

Thats basicly the validation logic

[#4]

Oleg,

I'm trying to do something similar to your example above. Do you have a working example by any chance?

Thanks,

Art

[amien]

Nope .. i dont have a solution for this 😞

[Oleg_Troyansky]

Folks,

please see an attached example with 2 ways of solving the problem in a load script - one is using previous() and peek() and another is using Mapping.

Amien - this problem needs to be solved in the script and not in chart expressions, because it has to do with poor data quality. As a "best practice", we try to cleanse data at the load time and not in the run time, whenever possible.

enjoy!