2 Replies Latest reply: Apr 3, 2017 6:07 AM by Jonah Van Quekelberghe RSS

    Correct/replace data in load script via separate Excel

    Jonah Van Quekelberghe

      Hello,

       

      I have an existing data model where there is data per month per account. For a certain dimension I would like to correct the source data via a separate Excel file (I could do this in the source data, but I rather have a clear view on the corrections done via this excel).

       

      How can I do this in the load script? Below you'll find a short example of what I want to do.

       

      For account 98745 the dimension was not correct in the source data for Period 201701 & 201702 (replace ABC with BCE), this will be given in the correction file.

       

       

      Source data (%PeriodAccount is a concattenated field)                

      PeriodAccountDimension%PeriodAccount
      20170398745BCE201703|98745
      20170312365DHE201703|12365
      20170298745ABC201702|98745
      20170212365DHE201702|12365
      20170198745ABC201701|98745
      20170112365DHE201701|12365

       

      Correction file

      Dimension corrected%PeriodAccount
      BCE201702|98745
      BCE201701|98745

       

      Corrected source data

      PeriodAccountDimension%PeriodAccount
      20170398745BCE201703|98745
      20170312365DHE201703|12365
      20170298745BCE201702|98745
      20170212365DHE201702|12365
      20170198745BCE201701|98745
      20170112365DHE201701|12365

       

       

      Thanks in advance!