Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

davidmcfall
New Contributor

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

Re: MAPPING old records to new records.

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

Re: MAPPING old records to new records.

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
Valued Contributor II

Re: MAPPING old records to new records.

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

davidmcfall
New Contributor

Re: MAPPING old records to new records.

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.

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

Re: MAPPING old records to new records.

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

davidmcfall
New Contributor

Re: MAPPING old records to new records.

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.