Correction excel file

[paullawrence190]

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

[marcus_malinow]

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

[paullawrence190]

Thanks Marcus just what i needed to get me started.

[paullawrence190]

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.

[marcus_malinow]

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

[paullawrence190]

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