topic Re: Error loading Prices in excel in QlikView

Error loading Prices in excel

ecabanas — Wed, 28 Sep 2016 10:37:19 GMT

I have this table in XLSX format

Article Code	Proveedor	Tarifa	Fecha
10365792	PROV00135	1,950	01/01/2016
21125027	PROV00135	1,660	01/01/2016
21125015	PROV00135	1,950	01/01/2016
21125256	PROV00135	1,660	01/01/2016
21125009	PROV00135	1,780	01/01/2016
21130221	PROV00135	2,900	01/01/2016
21132085	PROV00135	1,850	01/01/2016
21127632	PROV00135	2,340	01/01/2016
10371175	PROV00135	1,150	01/01/2016
10371176	PROV00135	1,560	01/01/2016
21125268	PROV00135	1,690	01/01/2016
21127629	PROV00135	1,690	01/01/2016
21125023	PROV00135	1,330	01/01/2016
21125013	PROV00135	1,060	01/01/2016
21127630	PROV00135	1,690	01/01/2016
10358496	PROV00135	2,450	01/01/2016
21130218	PROV00135	2,130	01/01/2016
21125251	PROV00135	2,450	01/01/2016
21127633	PROV00135	1,710	01/01/2016
21130299	PROV00135	2,300	01/01/2016
10364731	PROV00135	1,610	01/01/2016
10364729	PROV00135	2,520	01/01/2016
10364728	PROV00135	1,310	01/01/2016
10364730	PROV00135	1,580	01/01/2016
10364732	PROV00135	1,340	01/01/2016
21124836	PROV00135	1,650	01/01/2016
10360780	PROV00135	1,650	01/01/2016
21129752	PROV00135	1,460	01/01/2016
10371177	PROV00135	1,690	01/01/2016
10371179	PROV00135	1,260	01/01/2016
21127597	PROV00135	8,000	01/01/2016
21132084	PROV00135	3,000	01/01/2016
10368265	PROV00135	3,000	01/01/2016
10358496	PROV00135	2,950	01/01/2016
10369362	PROV00135	3,440	01/01/2016

but when I upload Qlik tranforms the tarifa field (price) in another completly different

Proveedor_Tarifa	From	ItemId	Tarifa
PROV00135	01/01/2016	10358496	0.002
PROV00135	01/01/2016	10358496	0.003
PROV00135	01/01/2016	10360780	0.002
PROV00135	01/01/2016	10364728	0.001
PROV00135	01/01/2016	10364729	0.003
PROV00135	01/01/2016	10364730	0.002
PROV00135	01/01/2016	10364731	0.002
PROV00135	01/01/2016	10364732	0.001
PROV00135	01/01/2016	10365792	0.002
PROV00135	01/01/2016	10368265	0.003
PROV00135	01/01/2016	10369362	0.003
PROV00135	01/01/2016	10371175	0.001
PROV00135	01/01/2016	10371176	0.002
PROV00135	01/01/2016	10371177	0.002
PROV00135	01/01/2016	10371179	0.001
PROV00135	01/01/2016	21124836	0.002
PROV00135	01/01/2016	21125009	0.002
PROV00135	01/01/2016	21125013	0.001
PROV00135	01/01/2016	21125015	0.002
PROV00135	01/01/2016	21125023	0.001
PROV00135	01/01/2016	21125027	0.002
PROV00135	01/01/2016	21125251	0.002
PROV00135	01/01/2016	21125256	0.002
PROV00135	01/01/2016	21125268	0.002
PROV00135	01/01/2016	21127597	0.008
PROV00135	01/01/2016	21127629	0.002
PROV00135	01/01/2016	21127630	0.002
PROV00135	01/01/2016	21127632	0.002
PROV00135	01/01/2016	21127633	0.002
PROV00135	01/01/2016	21129752	0.001
PROV00135	01/01/2016	21130218	0.002
PROV00135	01/01/2016	21130221	0.003
PROV00135	01/01/2016	21130299	0.002
PROV00135	01/01/2016	21132084	0.003
PROV00135	01/01/2016	21132085	0.002

thank's

Eduard

Re: Error loading Prices in excel

robert_mika — Wed, 28 Sep 2016 10:45:51 GMT

Works for me.

What are your transformation steps?

Could you post your script?

The fields name are different in both tables - is this intentional?

Re: Error loading Prices in excel

dickelsa — Wed, 28 Sep 2016 10:46:57 GMT

Hi Eduard,

Please provide us with a part of your script, so i can be sure of my response.

I think this has to do with the way you store your decimals in excel, and the way you load them in the script of Qlikview.

Try changing it to:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

(first four lines in script)

again, to be sure, I need to see the script.

Best regards,

Dick

Re: Error loading Prices in excel

ecabanas — Wed, 28 Sep 2016 10:50:14 GMT

Hi Dick

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='h:mm:ss';

Re: Error loading Prices in excel

qlikview979 — Wed, 28 Sep 2016 10:51:56 GMT

Hi ,

please provide the sample data xlsx

Regards,

Mahesh

Re: Error loading Prices in excel

ecabanas — Wed, 28 Sep 2016 10:58:28 GMT

You have the xlsx table in the beggining of the post

Re: Error loading Prices in excel

sunny_talwar — Wed, 28 Sep 2016 10:59:48 GMT

I think you need this:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

Re: Error loading Prices in excel

dickelsa — Wed, 28 Sep 2016 11:01:51 GMT

Eduard,

then try changing it to:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='h:mm:ss';

another solution is to multiply 'Tarifa' with 1000

so then youll get:

LOAD

Article Code

Proveedor,

Tarifa*1000 as tarifa,

Fecha

FROM TABLE.XLSX

Re: Error loading Prices in excel

Anil_Babu_Samineni — Wed, 28 Sep 2016 11:02:06 GMT

You might did one mistake with the Transformation, Can you please post your Application which you getting the wrong values

Try Robert and reply him. Because, That is simple loading