topic Checking for New/Deleted Records in QlikView

Checking for New/Deleted Records

Fri, 08 Jun 2012 14:38:57 GMT

Hello Everyone!

I am new to qlikview and am currently working on a difficult problem and I am in need of some suggestions.

What I am tasked to do is read in files where each file contains data from one day. I bring them in using a "for each next" loop and eventually, I am left with one qlikview table containing all the records from each day. My goal is to be able to select two dates and then compare the records from each. In particular, I am looking for which records are deleted and which records are new. To be clear, deleted records will not be found on a future file and new records will not be found on a past file.

My initial solution was to create a second qlikview table with all the field names renamed to Fieldname_2 and associatively join them through a common field name. That way, when I make my date selections, table 1 will house the first date's records and table 2 will house the second date's records. I would then check for where the length of an arbitrary field on table 2 was equal to zero and then return the corresponding value from the other table. This failed seemingly because qlikview was not recognizing where deleted records fell off or where new records would began.

I then tried forcing those new/deleted records to show themselves using recno() in the hopes that recno() would populate the unpopulated rows that correspond to the new/deleted records. This failed.

I then tried using the lookup() function which, as I read on here, is similar to the vlookup function in excel. This failed. (Quick note here, I was obviously able to find the deleted/new records in excel using the vlookup).

From there, I stumbled on the mapping load/applymap functions but that was also a bust.

I also used set analysis in order ignore one of the date selections made by the end user so that I'd compare one file date in one qv table against all the file dates in the other qv table. This has yet to work.

As a last ditched attempt, I tried joining the tables together in order to make it easier to sort and match but I can’t finish the reload presumably because the join is too large?

Once again, I am new to QV so some of the things I tried may have failed due to my inexperience so if anyone has any new ideas or ways to rework what I’ve already tried, I am all ears. If you have any questions I’ll try to answer them to the best of my ability. Thanks in advance for your help.

Checking for New/Deleted Records

jagan — Fri, 08 Jun 2012 19:03:42 GMT

Hi,

Try using Incremental loading option. You can find help regarding this in Qlikview help file in

Using QVD Files for Incremental Load.

Regards,

Jagan.

Checking for New/Deleted Records

Fri, 08 Jun 2012 20:04:18 GMT

I would suggest you look into the "Where Not Exists" statement.

Stephen

Re: Checking for New/Deleted Records

rwunderlich — Fri, 08 Jun 2012 21:13:21 GMT

That's a very interesting problem. I think it's a Set Analysis question -- what's the difference between two sets. Alternate States in V11 provide a simple approach to defining two sets and making the comparisons. See if the attached is close to what you want.

-Rob

http://robwunderlich.com

Checking for New/Deleted Records

Mon, 11 Jun 2012 15:30:53 GMT

Hi Rob,

I've been looking into Alternate States and I was really impressed by it. Unfortunately, my company won't be upgrading to QV 11 for some time. Is there any way to replicate the process in QV 10?

Re: Checking for New/Deleted Records

rwunderlich — Tue, 12 Jun 2012 05:15:11 GMT

Yes, you can replicate in V10. With V10, you have to load Date again into a island field (Date2) that will be used to make the Date2 selection. See attached.

-Rob

http://robwunderlich.com

Re: Checking for New/Deleted Records

Bruno_Calver — Fri, 23 Sep 2022 10:21:00 GMT

Hi All,

Great solution Rob as always, still relevant a decade later. I rebuilt the Alternate States version in Qlik Sense, so thought I would post it here for ease of reference.

Thanks,

Bruno