topic Re: MAPPING old records to new records. in QlikView

MAPPING old records to new records.

Anonymous — Fri, 22 Jul 2016 07:37:16 GMT

Hi,

I have a requirement to link transactions in an old ERP system, to records in a new ERP system, so that we can join purchasing history at the front end, for the end user. Example;

Old Name New Name New Name

OLDCODE OLDC001 Supplier Name

The issue we have is that the Supplier Name may change, so the name of the supplier may get updated in the new ERP, but not in the old ERP. So, I want to download the transactions from the old system, and if the vendor exists, overwrite the name of the supplier, with the most recent one, from the new ERP system. I've attempted a MAPPING script, which is attached - This successfully updates all of the old transactions, but then drops the new ones! Is there a mistake in the script which I'm missing?

Regards

David

Re: MAPPING old records to new records.

Fri, 22 Jul 2016 09:29:43 GMT

I am not sure about the data structure here, it might be due to '' used when applymap cannot find the old account code.

ApplyMap('NavCodeMap', [Old Account Code], '')

Re: MAPPING old records to new records.

qliksus — Fri, 22 Jul 2016 09:32:02 GMT

You code looks OK . Can you elaborate on what gets dropped ? as you haven't used any DROP statement and you are simply appending the newcode with the oldcode

Re: MAPPING old records to new records.

Anonymous — Fri, 22 Jul 2016 09:41:23 GMT

Sorry, the use of the word drop was unintentional. We moved systems in December 2015, so any transactions before then are correctly updated with the most recent vendor name. Any transactions from the new system have a blank vendor name.

Re: MAPPING old records to new records.

sunny_talwar — Fri, 22 Jul 2016 10:55:25 GMT

I think the issue is in your ApplyMap function, you are assigning a blank space when a old account is not found in the new one

VendorLedgerData:

LOAD [Old Account Code],

Year,

Month,

Amount,

ApplyMap('NavCodeMap', [Old Account Code], '') AS [Account Code],

ApplyMap('NavNameMap', [Old Account Code], '') AS Vendor

FROM

\\HDG-FILE\Users2\Qlik\MyFile2.qvd (qvd);

By Default ApplyMap will put the value of Old Account Code if it doesn't find a match, so for your 1st apply map you might not even need anything. For second one, you will need the old vendor field within MyFile2.qvd. So something like this:

VendorLedgerData:

LOAD [Old Account Code],

Year,

Month,

Amount,

ApplyMap('NavCodeMap', [Old Account Code]) AS [Account Code],

ApplyMap('NavNameMap', [Old Account Code], [Vendor Field From MyFile2.qvd]) AS Vendor

FROM

\\HDG-FILE\Users2\Qlik\MyFile2.qvd (qvd);

Here [Vendor Field From MyFile2.qvd] is a placeholder for your field name and replace to get the old vendor name when new one isn't available.

HTH

Best,

Sunny

Re: MAPPING old records to new records.

danansell42 — Fri, 22 Jul 2016 11:27:42 GMT

Is it because you are not bringing in the vendor name in the 2nd part of the script?

Concatenate

LOAD No_ AS [Account Code],

[Old Account Code],

Year,

Month,

Amount,

Need to add vendor name here

FROM

\\FILE\users2\Qlik\MyFile3.qvd (qvd);

Re: MAPPING old records to new records.

Anonymous — Fri, 22 Jul 2016 12:41:39 GMT

Yes, this has solved one of the issues, which made the other issue clear.

I was not bringing the name of the vendor through from Myfile as it clashed with Vendor in the map. However, if i changed vendor to "name" and brought through the data as "name" to match, the data now works.