Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
paullawrence190
Contributor
Contributor

Correction excel file

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

5 Replies
marcus_malinow
Specialist III
Specialist III

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
Contributor
Contributor
Author

Thanks Marcus just what i needed to get me started.

paullawrence190
Contributor
Contributor
Author

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
Specialist III
Specialist III

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
Contributor
Contributor
Author

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