Converting Text to Number

[#1]

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

[swuehl]

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

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

[swuehl]

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.

[MarcoWedel]

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

[effinty2112]

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