Function " where NOT Exists" to concatenate load

[francesctesi]

hi everyone,

to do an incremental load from a view on postgresql, I only process data from the last 7 days.

Then i would like to add with a concatenate load, all the rows which are not inserted in the QVD file.

I would like to use this script, but continue to give me error, because I do not enter any parameters in the 'NOT Exists' function.

//////

CONCATENATE LOAD
*
From [$(vQvdPath)/Exam.qvd] (QVD)
where NOT Exists ();

///////

The problem is that I never know exactly which field is different, as I do not have a key field.

Is it possible to do a check on the entire row to see if it exists, and if so add it to the QVD?

[maxgro · Accepted solution]

try with autonumber, something like

MyTable:
load
field1, field2,
AutoNumber(field1 &'|'& field2) as all_fields_key
;
// replace the inline load with a read from the postgres database (select .... from ...)
load field1, field2
inline [
field1,field2
2022-08-23,5
2022-08-25,6
2022-08-26,5
];

// replace the inline load with a read from qvd
CONCATENATE (MyTable)
load
field1, field2
inline [
field1,field2
2022-08-23,5
2022-08-25,6
2022-08-26,5
2022-08-26,5
2022-08-26,5
2022-08-26,5
2022-08-26,5
2022-08-26,500
]
Where
not exists(all_fields_key, AutoNumber(field1 &'|'& field2))
;

drop field all_fields_key;

// store MyTable into qvd

[francesctesi]

thanks so much, it works!