topic Re: Load number as string in QlikView

Load number as string

julruiz123 — Thu, 10 Nov 2011 14:28:38 GMT

Hi,

I have the next situation, in a database is stored the time as follow

9.01

6.45

7.10

8.10

So i need replace point by colon, but the string change as follows

7:1

8:1

How i can prevent the zero runs out?

Thanks.

Load number as string

its_anandrjs — Thu, 10 Nov 2011 14:32:11 GMT

Hi,

Use Replace command like

Len(Replace( YourTimeField, ' . ', ' : ' ) ) = 3

Rgds

Anand

Re: Load number as string

swuehl — Thu, 10 Nov 2011 14:42:45 GMT

I would suggest parsing your time in as Time format, like

LOAD

Time(Time#(YOURTIMEFIELDSTRING,'hh.mm'),'hh:mm') as TimeField,

...

FROM ...;

Hope this helps,

Stefan

edit: corrected time format output

Load number as string

julruiz123 — Thu, 10 Nov 2011 15:24:48 GMT

Hi,

It doesn't work the two alternatives.

The strange thing is tha i try with

Text(TimeField) but the result is the same

7:1

8:1

Load number as string

swuehl — Thu, 10 Nov 2011 15:32:28 GMT

Does the string change only after you applied Replace() function or if you just read it in from data base?

Could you check if you get a string or a numerical returned?

If you use something like

num(TimeField,'00.00') as TimeField

You should get two decimal places. if you the use my above suggestion, you should get what you want. But maybe I just don't get what's going on. Could you post a small sample?

Regards,

Stefan

Load number as string

Thu, 10 Nov 2011 16:15:21 GMT

Might not be the correct way, but in the object that you have this field in go to the Number tab, set it Fixed to 2 decimals, and then change the Decimal separator to :

Load number as string

ToniKautto — Thu, 10 Nov 2011 22:02:40 GMT

Please provide a sample of your script when you get the unexpected value, so that we can make a better judgement of what you need to do.

Re: Load number as string

julruiz123 — Fri, 11 Nov 2011 01:21:44 GMT

Hi,

This is the example.

When i try to modify the field trahoi the zero runs out.

Thanks

Re: Load number as string

ToniKautto — Fri, 11 Nov 2011 08:20:26 GMT

Please try unfromatting your loaded fields.

1. Settings > Document Properties > Number

2. Select the field of your choice, in this case trahoi

3. Klick the Default from Input button and you will see that the format now changed to the value stored in the QVD, which is a two decimal numeric value

4. I you want QlikView to always load the source format, you should also uncheck the Survive Reload checkbox

Please notice that you can override the document's setting on a object level, in the object properties' number tab. In case your document settings look ok while your object presents the value incorrectly you should check the object level settings.

---

Using QV10 SR3 there is no problem with setting the replacement already in the load script. Are you using an other version?

LOAD

Replace(col, '.', ':') as colon

Inline [

col

9.01

6.45

7.10

8.10

];

Re: Load number as string

SunilChauhan — Fri, 11 Nov 2011 08:29:13 GMT

see the attached file

hope this help.

if any thing else then let me know

Re: Load number as string

julruiz123 — Fri, 11 Nov 2011 15:12:17 GMT

This is the script that i tried

A01:

LOAD tradoc,

trahoi,

Time(Time#(trahoi,'hh.mm'),'hh:mm TT') as trahoi1

FROM

INMTRA_EXT_Copia.qvd

(qvd);

and this is the result

Re: Load number as string

julruiz123 — Fri, 11 Nov 2011 15:34:23 GMT

Hi,

This is the solution

Time(Time#(Num(trahoi,'00.00'),'hh.mm'),'hh:mm TT') as trahoi1

Thanks for the help!