topic Re: Converting Text to Number in QlikView

Converting Text to Number

Wed, 25 Nov 2020 16:16:04 GMT

Hi,
Im in the process of pulling web data and the data is being pulled all in text format, i am looking for a way to change the Min and Max temperature from the weather tables i have pulled into Num format so i can put it into a chart as currently it doesn't work.

LOAD @1 as PH_Direction,

@2 as PH_Speed,

@3 as PH_Time,

@4 as PH_UV,

@5 as PH_RAmount,

@6 as PH_CoR,

@7 as PH_Min,

@8 as PH_Max,

@9 as PH_Summary,

@10 as PH_Date

This is what i currently have for this section. PH_Min and PH_Max are Min and Max temperatures.

Cannot seem to find an appropriate answer on the community

Re: Converting Text to Number

trdandamudi — Thu, 19 May 2016 18:08:21 GMT

if you want to convert into numeric then:

Num(fieldName)

Re: Converting Text to Number

swuehl — Thu, 19 May 2016 18:12:34 GMT

Look into number interpretation functions like Num#(), for example

LOAD

Num#(@7,'#,00',',','.') as PH_Min,

Num#(@8,'#,00',',','.') as PH_Max,

On Format Codes for Numbers and Dates

Re: Converting Text to Number

Thu, 19 May 2016 18:14:59 GMT

Thanks, it seems to be working however since the data being pulled is displayed like this
19°C
it displays it as a "-" in the table.
How do i format it to remove the " °C " at the end

Re: Converting Text to Number

MarcoWedel — Thu, 19 May 2016 18:17:51 GMT

can you post a sample of your data and your format variables as well?

thanks

regards

Marco

Re: Converting Text to Number

MarcoWedel — Thu, 19 May 2016 18:21:37 GMT

PurgeChar(@7,'°C') as PH_Min

Re: Converting Text to Number

swuehl — Thu, 19 May 2016 18:22:32 GMT

Maybe use purgechar()

PurgeChar(FIELD,'°C') As FIELD

Re: Converting Text to Number

MarcoWedel — Thu, 19 May 2016 18:22:56 GMT

or to keep the unit but get a dual value (incl. underlying numerical value):

Dual(@7, PurgeChar(@7,'°C')) as PH_Min

hope this helps

regards

Marco

Re: Converting Text to Number

Thu, 19 May 2016 18:27:52 GMT

Im having difficulty uploading my QVW file as there doesnt seem to be anywhere to do it, but my entire script for that section is

//......................................Port Hedland

LOAD @1 as PH_Direction,

@2 as PH_Speed,

@3 as PH_Time,

@4 as PH_UV,

@5 as PH_RAmount,

@6 as PH_CoR,

@7 as PH_Min,

@8 as PH_Max,

@9 as PH_Summary,

@10 as PH_Date

FROM

[http://www.weatherzone.com.au/wa/pilbara/port-hedland]

(html, codepage is 1252, no labels, table is @6, filters(

Remove(Row, Pos(Top, 13)),

Remove(Row, Pos(Top, 12)),

Remove(Row, Pos(Top, 8)),

Remove(Col, Pos(Top, 15)),

Remove(Col, Pos(Top, 13)),

Remove(Col, Pos(Top, 11)),

Remove(Col, Pos(Top, 9)),

Remove(Col, Pos(Top, 7)),

Remove(Col, Pos(Top, 5)),

Remove(Col, Pos(Top, 3)),

Rotate(right),

Remove(Row, Pos(Top, 1))

));

PH_Date	PH_Summary	PH_Max	PH_Min	PH_CoR	PH_Direction	PH_RAmount	PH_UV	PH_Speed
FriMay 20	Sunny	33°C	21°C	20%	E	< 1mm	High	13km/h
SatMay 21	Increasing sunshine	31°C	22°C	10%	WNW	< 1mm	Very High	9km/h
SunMay 22	Mostly sunny	32°C	19°C	30%	SSE	< 1mm	High	20km/h
ThuMay 26	Sunny	30°C	20°C	40%	SSE	< 1mm		9km/h
MonMay 23	Sunny	32°C	20°C	30%	ESE	< 1mm	High	28km/h
TueMay 24	Sunny	31°C	19°C	10%	ESE	< 1mm	High	15km/h
WedMay 25	Sunny	30°C	21°C	30%	SE	< 1mm		10km/h

So i would like to be able to have the temperatures and other numbers in a number format so i can chart them. if i format them at the moment using Num() the field will show up as "-" so i need to be able to remove °C from each

Re: Converting Text to Number

Thu, 19 May 2016 18:36:55 GMT

The dual didn't seem to do anything? although the PurgeChar by itself seemed to work perfectly!

Re: Converting Text to Number

swuehl — Thu, 19 May 2016 18:51:58 GMT

Num() is for formatting numbers (text representation), Num#() to interpret text as number:

= Num(Num#('19 °C','# °C'),'#.00')

Re: Converting Text to Number

swuehl — Thu, 19 May 2016 18:54:46 GMT

Using the suggested expression with Dual(), you will still keep the text, but fill in the numeric representation with the parsed number, so you can aggregate the values using sum(), Avg() etc.

To Keep the text,

Num#('19 °C','# °C')

should work as well.

But I assume your temperate values should get aggregated anyway, so I would do the formatting in the chart expressions / chart objects.

Re: Converting Text to Number

MarcoWedel — Thu, 19 May 2016 23:01:29 GMT

Hi,

maybe like this:

table1:
LOAD SetDateYear(Date#(Mid(PH_Date,4),'MMM DD'),Year(Today(1))) as PH_Date,
    PH_Summary,
    Dual(PH_Max, PurgeChar(PH_Max,'°C')) as PH_Max,
    Dual(PH_Min, PurgeChar(PH_Min,'°C')) as PH_Min,
    PH_CoR,
    PH_Direction,
    PH_RAmount,
    PH_UV,
    Dual(PH_Speed, PurgeChar(PH_Speed,'km/h')) as PH_Speed
FROM [https://community.qlik.com/thread/217596] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Re: Converting Text to Number

effinty2112 — Sun, 22 May 2016 10:39:38 GMT

Hi Jake,

Using the Num# function in the script will let QV interpret the temperature data correctly.

Num#(@7,'0°C') as PH_Min,

Num#(@8,'0°C') as PH_Max,

Cheers

Andrew