Help with concatenate data and script

Thu, 04 Aug 2016 01:15:16 GMT

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

Re: Help with concatenate data and script

kaushiknsolanki — Thu, 04 Aug 2016 06:39:21 GMT

Hi,

Just try

Right([Cost Center],8)

Qlik Will convert it into Number automatically.

Regards,

Kaushik Solanki

topic Help with concatenate data and script in QlikView

Help with concatenate data and script

Re: Help with concatenate data and script