topic gettings date from record above in QlikView

gettings date from record above

amien — Sat, 17 Apr 2010 22:11:18 GMT

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)

gettings date from record above

Oleg_Troyansky — Sat, 17 Apr 2010 23:59:44 GMT

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,

gettings date from record above

amien — Sun, 18 Apr 2010 20:18:32 GMT

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

gettings date from record above

Fri, 13 Aug 2010 21:46:26 GMT

Oleg,

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

Thanks,

Art

gettings date from record above

amien — Sat, 14 Aug 2010 07:57:37 GMT

Nope .. i dont have a solution for this 😞

gettings date from record above

Oleg_Troyansky — Sat, 14 Aug 2010 20:47:51 GMT

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!