Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kkkumar82
Specialist III
Specialist III

Need help in understanding Delete logic in qvc.qvs

Hi all,

I tried to understand the Delete logic given in qvc.qvs file of Rob Wunderlich, but couldn't identfify how it is working.

In general I would go with a inner join on the primary key columns but here the way is different.

Please find the highlighted code for delete logic.

delete.png

Also find attached qvw by Rob.

rwunderlich

8 Replies
swuehl
MVP
MVP

To understand the full logic, I would suggest that you look into the componentes library, look into the code executed by

CALL Qvc.IncrementalSetup ('TableWithId', 'RecordId', 'RecordId');

and

CALL Qvc.IncrementalStore

kkkumar82
Specialist III
Specialist III
Author

Hi Swehl,

I have debugged by I am not sure which value to give like 21 given above. The sample given by Rob has 31 records and the sample which I took has records and after updation and it has only 6.

Insert and Update logic are working fine but when I am deleting entire record it is still showing up in the final qvd.

swuehl
MVP
MVP

Ok, I see what you mean.

Not sure why Rob used a hardcoded RateID = 21 to filter the primary key here.

Maybe just to demo something and then forgot it to remove the WHERE clause?

As far as I understood, you should load all current primary keys from your source DB, if you want to delete rows in your incrementally loaded data set, including historic data rows.

Let's wait if he can shed some light on this.

kkkumar82
Specialist III
Specialist III
Author

Just curious , when we want to remove the deleted rows we have to do a inner join of the primary key ids with the newly inserted and Deleted output , but I couldn't see it here.

Have you anytime gone through the debugging of qvc.qvs .

May be now ??

swuehl
MVP
MVP

I haven't 'debugged' it, but you can find the INNER JOIN around line 1699, in the SUB Qvc.UpdateQVD

REM If deletes requested;

IF len('$(_vDbKeyTable)') > 0 AND $(_vQvdExists) THEN

  Call Qvc.Log ('Before deletes: $(_vTablename) rows=' & num(NoOfRows('$(_vTablename)'), '#$(ThousandSep)##0'));

  LET _vDbKeyTable_Fieldname = FieldName(1,'$(_vDbKeyTable)');

  INNER JOIN ([$(_vTablename)]) LOAD [$(_vDbKeyTable_Fieldname)]  AS [$(_vPrimaryKey)] RESIDENT [$(_vDbKeyTable)];

  Call Qvc.Log ('After deletes: $(_vTablename) rows=' & num(NoOfRows('$(_vTablename)'), '#$(ThousandSep)##0'));

  DROP TABLE [$(_vDbKeyTable)]; // Drop the Key Table

  SET _vDbKeyTable_Fieldname=;

ENDIF

kkkumar82
Specialist III
Specialist III
Author

Thanks Swehl ,

I just got it if I remove the where clause.

Will wait for Rob for any further comments on this.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

The

WHERE RateId < 21

when loading the Primary Keys is just a bit of fakery for the example. I am simulating that some rows have been deleted from the source. I've added a comment to that effect in the example.

The example wasn't producing the results I expected because I had not noticed the impact of the Qvc.Loader.v.StoreMaxModFieldValue feature. So it was not retrieving all the "new" rows on a subsequent run. This should not have been a problem with real data, it's just the trickery I was playing to be able to use the same files over and over for example. I've updated the example to fix this as well.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Stefan, you're on the team

-Rob