topic Problem with loading data from excel in a specific format in QlikView

Problem with loading data from excel in a specific format

francisvandergr — Thu, 12 May 2011 09:18:29 GMT

I have a field in Excel on Column 3. When i load this field in QV. QV load this field with decimals. From another database i have this field without decimals.

How can i load an excel field with no decimals.

The first line you can see in the coumn nummer 7.00000 I want this number as 7.

How can i do this ?

Problem with loading data from excel in a specific format

stephencredmond — Thu, 12 May 2011 09:40:33 GMT

Hi Francis,

Probably just easy to apply an integer format to the number on the way in:

Num(@3, '#.##0', ',', '.') as Chauffeur\Nummer

You could also set the Number format at the Document Properties level but I have found that this does not always hold between reloads.

Regards,

Stephen

Problem with loading data from excel in a specific format

Thu, 12 May 2011 09:51:32 GMT

Hi Francis,

I'm a complete noob but I know excel so thought I would give this a try The cell format is set to TEXT. Highlight column C and change format to GENERAL. I think this should sort your problem out.

Also setting the cell to general means that if the cell was a formula you can copy that formula down. When a cell is set to text it only pastes the value. Let me know if this resolves your problem. If you still decimals in QV try using the following syntax in the edit expression part of your dimension

num({field name}, '0')

Re: Problem with loading data from excel in a specific format

Thu, 12 May 2011 09:52:41 GMT

Hello Francis,

I tested it a little while. As Stephan mentioned the Number format settings in the Doc Props do not always help.

Take a look into my exam app (and look at Tbl2). Sometimes the (script-) world is a difficult one ...

HtH

Roland