Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
Hi,
Try to remove this
SET TimeFormat='h:mm:ss TT';
from your script..
HTH
Sushil
It gets even weirder, as I found out this does not always happen, which makes it even more random.
Hi,
Try using Text().
Load
Text(Value) AS Value
FROM Datasource;
Regards,
Jagan.
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,
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
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.
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