Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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], '')
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
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.
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
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);
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.