Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

String/Number format problem in Qlikview

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

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?

8 Replies
swuehl
MVP
MVP

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.

sushil353
Master II
Master II

Hi,

Try to remove this

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

from your script..

HTH

Sushil

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try using Text().

Load

     Text(Value) AS Value

FROM Datasource;

Regards,

Jagan.

swuehl
MVP
MVP

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,

Not applicable
Author

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

Not applicable
Author

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.

nitin_2409
Contributor
Contributor

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