How to exclude few records from existing QVD

[raju_salmon]

Hi,

I have an incremental scenario where i need to cross check inserted block records are available or not in full qvd and if available i need to exclude them before merge them.

I cannot use where not exist as i do not have primary key.

Here is scenario, i have one field which is FileRecievedDate where everyday we get one file and all records of that file contains that date. Sometimes we may get old file with few updates, and in DB those records gets replaced which we need to deal in QV.

Example:

Name  Salary ModifieldDatetime FileRecievedDatetime

Full load: I created QVD with 100 records (Jan1st to jan20th)

Nextday: Recived 20 records where as 10 records belongs to old file (i.e. Jan15th)

Incremental - 20

concatenate

full - 100 which should exclude 10 records of Jan15th

output should be 110 records.

Any suggestion here could help me to do this? Do we have any command like SQL Exclude we can use here?

Thanks,

Raju

[avinashelite]

Sorry for delay in my response, I could not see any primary key  in your data set so that I could base the incremental load ...we have the updated date that is fine but in order to reduce the data from history QVD we need a key so that it will help you identify the updated records which is lacking in your data set

[raju_salmon]

Thank you Avinash. You are true. Even i cannot able to create composite key, all possible values are giving me duplicate somewhere...

I have only one option, from informatica side, team is deleting a specific day data and inserting the same data if they receive file again.

Means my full qvd have data (from Jan1st to Ja10th in FileRecievedDate), if i try to load next day i shoud get jan 11th (inserted), but if i receive other old dates ex: jan2nd...so what i need to do here...

First i need to take distinct dates from inserted block, before merge this with full QVD, i need to exclude the data of these dates (if available).

My concern here is how to exclude this?

It would be great if you can give any input...

[jonathandienst · Accepted solution]

Assuming the incremental file replaces all the data for a day, load the incremental file first and create a loaded field to track the loaded dates;

LOAD ....

  InfoDate,                                // your date field - modify to suit your data set

  InfoDate as loadedDate

FROM Incremental.csv (....);     // the incremental source, modify to suit

Then load the main data excluding the loaded dates:

LOAD ...

  InfoDate,                               // modify to suit

FROM Stored.qvd (qvd)

Where Not Exists(loadedDate, InfoDate);    // the stored qvd

[raju_salmon]

Thank you lot Janathan.

I know about where not exists (primarykey), however when it duplicates do we need to take where not exists (field1,field2) where field1 and field2 have same information?

I am confusing how it work when it duplicates. I will check the exist command functionality. Thanks for your help on this.

[raju_salmon]

Awesome Jonathan. I tried to apply the same logic you suggestion and working fine.