topic Re: Correction excel file in QlikView

Correction excel file

paullawrence190 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Correction excel file

marcus_malinow — Tue, 27 Jun 2017 09:09:35 GMT

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

Re: Correction excel file

paullawrence190 — Tue, 27 Jun 2017 09:12:32 GMT

Thanks Marcus just what i needed to get me started.

Re: Correction excel file

paullawrence190 — Wed, 28 Jun 2017 14:32:42 GMT

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.

Re: Correction excel file

marcus_malinow — Wed, 28 Jun 2017 14:47:52 GMT

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

Re: Correction excel file

paullawrence190 — Wed, 28 Jun 2017 15:23:14 GMT

Thanks Marcus that was it works a treat now. Need to do a bit of data validation but looks good.