6 Replies Latest reply: Jul 22, 2016 8:41 AM by David McFall RSS

    MAPPING old records to new records.

    David McFall

      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.
          youyun Jupiter

          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.
            susant Kumar swain

            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.
              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

              • Re: MAPPING old records to new records.
                Daniel Ansell

                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.
                  David McFall

                  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.