11 Replies Latest reply: Nov 11, 2011 10:34 AM by Felipe Ruiz

Load number as string

Felipe Ruiz Nov 10, 2011 9:28 AM

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

Anand Chouhan Nov 10, 2011 9:32 AM (in response to Felipe Ruiz )

Hi,

Use Replace command like

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

Rgds
Anand
Like Show 0 Likes (0)

Actions
Re: Load number as string

Stefan Wühl Nov 10, 2011 9:43 AM (in response to Felipe Ruiz )

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
Like Show 0 Likes (0)

Actions
- Load number as string
  
  Felipe Ruiz Nov 10, 2011 10:24 AM (in response to Stefan Wühl )
  
  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
  
  Like Show 0 Likes (0)
  
  Actions
  - Load number as string
    
    Stefan Wühl Nov 10, 2011 10:32 AM (in response to Felipe Ruiz )
    
    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
    
    Like Show 0 Likes (0)
    
    Actions
  - Load number as string
    
    Toni Kautto Nov 10, 2011 5:02 PM (in response to Felipe Ruiz )
    
    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.
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: Load number as string
      
      Felipe Ruiz Nov 10, 2011 8:21 PM (in response to Toni Kautto )
      
      Hi,
      
      This is the example.
      
      When i try to modify the field trahoi the zero runs out.
      
      Thanks
      
      Prueba.qvw 1.0 MB
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: Load number as string
        
        Toni Kautto Nov 11, 2011 3:53 AM (in response to Felipe Ruiz )
        
        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
        ];
        
        Like Show 0 Likes (0)
        
        Actions
Load number as string

Marc Livingston Nov 10, 2011 11:15 AM (in response to Felipe Ruiz )

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 :
Like Show 0 Likes (0)

Actions
Re: Load number as string

Sunil Chauhan Nov 11, 2011 3:29 AM (in response to Felipe Ruiz )
see the attached file
hope this help.

if any thing else then let me know

Prueba.qvw 132.2 K
Like Show 0 Likes (0)

Actions
- Re: Load number as string
  
  Felipe Ruiz Nov 11, 2011 10:12 AM (in response to Sunil Chauhan)
  
  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
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Load number as string
    
    Felipe Ruiz Nov 11, 2011 10:34 AM (in response to Felipe Ruiz )
    
    Hi,
    
    This is the solution
    
    Time(Time#(Num(trahoi,'00.00'),'hh.mm'),'hh:mm TT') as trahoi1
    
    Thanks for the help!
    
    Like Show 0 Likes (0)
    
    Actions

Go to original post