topic Re: String/Number format problem in Qlikview in QlikView

String/Number format problem in Qlikview

Thu, 04 Apr 2013 10:31:28 GMT

Hey all,

I have a problem where Qlikview seems to randomly decide which kind of format to use for my data.

I get my data out of SQL, and the results from athletes have a particular format in which the results are shown. This works all fine.

So f.e.

My SQL Data has results like this (in seconds), and it's saved as a string.

53.00

53.20

53.55

All fine there.

But when I load this into Qlikview I get the following:

53.20

53.55

So somehow Qlikview decides to convert the 53.00 string into an integer, but it decides NOT to convert the 53.20 into a decimal.

One of the problems is that I can't just give an number format input for the entire field, because results like 1:03.55 (1 minute, 3.55 sec) or 1:44:23 (1 hours, 44 min, 23 sec) also have to be possible.

So my question is: How do I stop Qlikview from chosing the format itself, and instead just let Qlikview accept the string record as a string.

Does anyone know how to solve this?

Re: String/Number format problem in Qlikview

swuehl — Thu, 04 Apr 2013 11:12:20 GMT

I would suggest reading your data in using interval#() interpretation function:

LOAD

interval#(FIELD,'mm:ss.ff') as FIELD,

...

FROM ....;

So you can e.g. use calculation with your FIELD, like an average.

Re: String/Number format problem in Qlikview

sushil353 — Thu, 04 Apr 2013 11:29:46 GMT

Hi,

Try to remove this

SET TimeFormat='h:mm:ss TT';

from your script..

HTH

Sushil

Re: String/Number format problem in Qlikview

Thu, 04 Apr 2013 11:31:16 GMT

It gets even weirder, as I found out this does not always happen, which makes it even more random.

Re: String/Number format problem in Qlikview

jagan — Thu, 04 Apr 2013 11:37:09 GMT

Hi,

Try using Text().

Load

Text(Value) AS Value

FROM Datasource;

Regards,

Jagan.

Re: String/Number format problem in Qlikview

swuehl — Thu, 04 Apr 2013 11:51:45 GMT

One of the problems is that I can't just give an number format input for the entire field, because results like 1:03.55 (1 minute, 3.55 sec) or 1:44:23 (1 hours, 44 min, 23 sec) also have to be possible.

Still suggesting a QV interpretation function, to convert your strings to numbers.

If you need to handle different input formats, look into alt() function, like

interval( alt( interval#(FIELD,'mm:ss.ff'), interval#(FIELD,'hh:mm:ss'), 'No matching format: '&FIELD ),'hh:mm:ss.ff') as FIELD,

Re: String/Number format problem in Qlikview

Thu, 04 Apr 2013 12:17:50 GMT

Very helpful! Thanks for that.

The only down side is that we use an ETL load and normally in the extract layer we simply load everything from SQL. So when it gets to the transform layer the field is already screwed. But I'm sure we'll find a way to work around this, or simply make an exception for this application

Re: String/Number format problem in Qlikview

Thu, 04 Apr 2013 12:20:03 GMT

Interesting...

But is it also possible to use more than 1 format for one field?

In this case we'd need atleast ss.ff, m:ss.ff, h:mm:ss as possible formats, so if only one format per field is possible then this will not work. But I do like that it has the possibility of making calculations, I'll certainly look into that later.

Re: String/Number format problem in Qlikview

nitin_2409 — Tue, 16 Oct 2018 13:52:13 GMT

Hi Pim,

Just wondering if you got any workaround for this issue, i am struggling with the same problem in qliksense.

Link to my query:

string format issue in qliksense

Thank you

Nitin