Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a Main.qvd file which holds Incident details for all incident.Each Incident have 4 records. And I have one Delta.qvd which is having 1 incident with 4 updated records.
I want to replace all 4 records of Main.qvd with the 4 records of Delta.qvd.
Not exists function is not working. It is replacing duplicate records.
Please help.
Thanks,
Sarif
Replace the inline load with the load from your qvd
//
// Load the Delta
//
Delta:
load * inline [
incident, incidentdetail
1, a
1, b
1, c
1, d
2, a
2, b
2, c
2, d
];
Tmp: load incident as incident2 Resident Delta;
//
// add Main table
// exclude incident already loaded
//
Concatenate (Delta)
load * inline [
incident, incidentdetail
1, a
1, b
1, c
1, d
2, f
2, g
2, h
2, i
3, l
3, m
3, n
3, o
]
Where Not exists(incident2, incident);
DROP Table Tmp;
Load the records of the delta in a table and then add only the records from main that don't exist in delta. Then store the result as main.
Main:
LOAD * FROM Delta.qvd;
CONCATENATE Main
LOAD * FROM Main.qvd (qvd) WHERE NOT Exists(IncidentID);
STORE Main INTO Main.qvd (qvd);
Replace IncidentID with whatever field is used as primary key for the records.
Replace the inline load with the load from your qvd
//
// Load the Delta
//
Delta:
load * inline [
incident, incidentdetail
1, a
1, b
1, c
1, d
2, a
2, b
2, c
2, d
];
Tmp: load incident as incident2 Resident Delta;
//
// add Main table
// exclude incident already loaded
//
Concatenate (Delta)
load * inline [
incident, incidentdetail
1, a
1, b
1, c
1, d
2, f
2, g
2, h
2, i
3, l
3, m
3, n
3, o
]
Where Not exists(incident2, incident);
DROP Table Tmp;
Hi gysbert,
in this case we are missing the duplicate records. Lets say for Incident1, we have 4 records. If we use Not Exists then in the main qvd it is showing one records instead of 4 records after concatenate.
Amazing. You say you have four records in Delta.qvd and when you load all records from Delta.qvd you have only one record. That's truly astounding. Please share that qvd file with us.
Hello Maxgro,
This is working fine.
Could you please help me to understand the difference between Not Exists (Incident) and Not Exists (Incident2,Incident).
It would be really helpful if you can explain this.
Thanks a lot.
Sarif
You can find a detailed explanation in these docs
exists
not exists