Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a two excel files. One is a daily extract form our CRM, the other is a correction file with same file structure but with some corrections to errors on the CRM we cannot amend on the CRM.
Is there any way I can tell Qlik to use the clean file first but add in the rest of files from the extract, without getting duplicates.
Many thanks
Paul
Hi Paul,
yes!
Without knowing your exact file structure it's difficult to work out exactly what your script would look like but it might be something like this:
CRMData:
LOAD
[key field],
[field 1],
[field 2],
etc...
FROM CorrectionFile
;
CONCATENATE (CRMData)
LOAD
[key field],
[field 1],
[field 2],
etc...
FROM CRM Extract
WHERE NOT Exists ([key field])
;
Of course this is a simplification. Your key may be a combination of several fields, in which case you should build a composite key, and use that in your exists clause.
Marcus
Thanks Marcus just what i needed to get me started.
I have made a composite key but when i do this it does not work. is this because i am having to do a resident load?
If i use an existing key field it works but key is not unique so i loose some records. I have added this key to a date field to get unique key.
When i do via a temp table and resident i just get the correction file in the data but not he extract.
Hi Paul,
I imagine your resident load may be the issue, no problem though.
So, as things stand you have something like:
table1:
LOAD
[composite key]
...
from corrections
table2:
LOAD
field1 & '_' & field2 as [composite key]
...
from extract
concatenate (table1)
LOAD
[composite key]
...
RESIDENT table2
WHERE Not EXISTS ([composite key])
the issue here is that all possible values for composite key already exist in table 2.
So, move the exists clause to the table2 load
WHERE Not Exists([composite key], field1 & '_' & field2)
Is this something like what's in your code?
If not, please share a snippet.
Marcus
Thanks Marcus that was it works a treat now. Need to do a bit of data validation but looks good.