Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

MAPPING old records to new records.

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

1 Solution

Accepted Solutions
danansell42
Creator III
Creator III

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);

View solution in original post

6 Replies
Not applicable
Author

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], '')

qliksus
Specialist II
Specialist II

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

Anonymous
Not applicable
Author

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.

sunny_talwar

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

danansell42
Creator III
Creator III

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);

Anonymous
Not applicable
Author

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.