topic Re: Qlik SENSE : Problem to display a numeric column after loading in App Development

Qlik SENSE : Problem to display a numeric column after loading

fabop — Mon, 15 Jul 2019 07:52:53 GMT

I have two sqlserver columns loaded with the same format money but when i display them the result is different .

I don't find how to correct that.
Could you help me?

Thank you.

(Qlk SENSE Version : February 2019 Patch 2 – 13.9.3)

Re: Qlik SENSE : Problem to display a numeric column after loading

Lauri — Mon, 15 Jul 2019 16:47:11 GMT

What do those numbers look like in SQL Server?

Are you applying any formatting to B (during load or in your table)?

Re: Qlik SENSE : Problem to display a numeric column after loading

fabop — Tue, 16 Jul 2019 07:38:17 GMT

The two numbers (MASSE_SALARIALE_DADS and MASSE_SALARIALE_CDD) in SQL SERVER :

No format in the LOAD :

SELECT

       ent.CODE_ETABLISSEMENT         as code_etablissement,
don.ID_ANNEE          as annee_fiscale,

don.MASSE_SALARIALE_DADS        as masse_salariale_dads,
don.MASSE_SALARIALE_CDD         as masse_salariale_cdd,
        ent.id_site_plan         as id_site_plan,
        ent.id_geographie         as id_geo
FROM t_ETABLISSEMENT_ANNEE      don with (nolock)
inner join t_etablissement      ent with (nolock) on ent.id = don.id_etablissement;

Re: Qlik SENSE : Problem to display a numeric column after loading

Lauri — Tue, 16 Jul 2019 13:01:49 GMT

I would expect your load statement to be something like this:

Fiscal:

Load * ;

SQL SELECT
ent.CODE_ETABLISSEMENT         as code_etablissement,
don.ID_ANNEE          as annee_fiscale,
don.MASSE_SALARIALE_DADS        as masse_salariale_dads,
don.MASSE_SALARIALE_CDD         as masse_salariale_cdd,
        ent.id_site_plan         as id_site_plan,
        ent.id_geographie         as id_geo
FROM t_ETABLISSEMENT_ANNEE      don with (nolock)
inner join t_etablissement      ent with (nolock) on ent.id = don.id_etablissement;

(Although I wonder if Qlik understands the nolock option.)

Qlik normally imports numbers of any type as numbers, and you don't see problems. If your money fields in SQL Server have an unusual format, you can try to change them during the load:

Fiscal:

Load
code_etablissement,
annee_fiscale,
Num(masse_salariale_dads, '$#,###,##0'),
Num(masse_salariale_cdd, '$#,###,##0'),
id_site_plan,
id_geo;

If that does not correctly format it, then Qlik may think your money fields are strings (not numbers) because of funky characters (perhaps because of your European code set or money format (it would be helpful to see examples of your money as they appear in SQL Server). Then you can convert them using the Num# function (details).