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).
[Amount in local currency] as Amt,
[Profit Center] as ProfitCentre,
[Cost Center] as CostCentre_COde,
Account as GLAcct,
[Posting Date] as Cost_Date,
[Offsetting acct no.],
'ACTUAL' as CostType,
RowNo() as ID
[\\10.1.6.229\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,
'CHECK' as CostType
[\\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.