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

       

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

       

       

      Concatenate(Actuals)
      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,
      'CHECK'
      as CostType
      FROM
      [\\10.1.6.229\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.

       

      Thanks

      Joanna