1 Reply Latest reply: Aug 4, 2016 2:39 AM by Kaushik Solanki RSS

    Help with concatenate data and script

    Joanna Versiani

      Hello everyone,


      I have two spreadsheets which I have the follow scripts. (the purpose of bringing the second, concatenate data, is to check if data is complete as they run from different sources).


      LOAD Assignment,
      [Document Number],
      [Document Type],
      [Amount in local currency] as Amt,
      [Local Currency],
      [Profit Center] as ProfitCentre,
      [Trading Partner],
      [Cost Center] as CostCentre_COde,
      Account as GLAcct,
      [Posting Date] as Cost_Date,
      [Posting Date],
      [Company Code],
      [Offsetting acct no.],
      as CostType,
      RowNo() as ID
      [\\\work\QlikView\Corporate Accounting\SG&A Report\Actuals\Actual_*.XLSX]
      ooxml, embedded labels)
      WHERE [Company Code] <= 1704 and [Document Number]<>'';



      LOAD Account as GLAcct,
      NUM(Right([Cost Center],8)) as CostCentre_COde,
      makedate(left(Period,4),right(Period,2),'15') as Cost_Date,
      Amount as AmtAUD,
      as CostType
      [\\\work\QlikView\Corporate Accounting\SG&A Report\BPC SG&A, Net Sales & Ebitda.xlsx]
      ooxml, embedded labels, table is [SG&A Data]);


      The problem I am having is the cost center from the concatenate data has c_55121205 while in the actuals data only has 55121205, to solve this issue I have done the right formula in the script, as you can see above, 'NUM(Right([Cost Center],8)) as CostCentre_COde'.


      However, it is not bringing any values.


      Does anyone know how to help me? I believe that is the reason why is not working but it could be something else.