Solved: How to exclude few records from existing QVD - Qlik Community

raju_salmon · ‎2017-03-16

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

jonathandienst · ‎2017-03-22

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Anil_Babu_Samineni · ‎2017-03-16

You may look Join instead of Concatenate.

I am assuming, You have 100 Records then one more time 110 records are inserted. In that, 100 are common this case you need 110 records from that Fact. This is just assumption or else you may come up with Data and expected output.

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

avinashelite · ‎2017-03-16

check this ppt

raju_salmon · ‎2017-03-16

Thanks Avinash for ppt, my scenario is not regular one. Its is not covered under ppt.

I have 10 records and key as 1,1,1,1,1,1,1,1,1,1

1 have 100 records 1,2,1,2,1,1,1,1,1,2,2,2,2,2,.....100

From 100 records i just need to exclude first 10 records.

Which kind of join or function works to achieve this?

raju_salmon · ‎2017-03-16

Thanks for response.

Yes, thinking about join. But my scenario is like exclude in SQL... any alternate in QLik?

I have 10 records which i need to exclude from 100 records.

avinashelite · ‎2017-03-16

share the sample data for 2days to implement the incremental load ..so that we could help you out on the same

Anil_Babu_Samineni · ‎2017-03-16

I have 10 records which i need to exclude from 100 records.

From here we can do using Where Condition in script like

Load * from <Data Source> Where [Record Field] <> '','','','',......;

You may come back with data so then we can help you. I meant please share sample data and expected result

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

raju_salmon · ‎2017-03-16

Thanks anil, in that case in where class i need to use all values... they are not constant.

raju_salmon · ‎2017-03-16

Sure. PFA... Thank you so much.

raju_salmon · ‎2017-03-17

Anyone can help me here?