Crosstable load does not load the data as Num.

[tanmayNobel]

Hi All, 

I have unpivoted my data and the cross table does not load the data as num.  I did num() and num#() too.

Unqualify *;

LIB CONNECT TO [Nobel redshift];

[scadarowd_flow]:
CROSSTABLE ([scadarowd_flow Tags],[scadarowd_flow Data],2)
LOAD
[datestamp] AS [datestamp1],
[rowl.jlv1_flow.f_cv],
[rowl.jlv2_flow.f_cv],
[rowl.lhhw_flow.f_cv],
[rowl.nogalesls_flow.f_cv],
[rowl.pm22_flow.f_cv],
[rowl.pm22_flow_spt.f_cv],
[rowl.pm9_flow.f_cv],
[rowl.rs01_flow.f_cv],
[rowl.rs06_flow.f_cv],
[rowl.rs10_flow.f_cv],
[rowl.rs12_flow.f_cv],
[rowl.rs2_zoneii_flow.f_cv],
[rowl.rs2_zoneiii_flow.f_cv],
[rowl.rs2a_bypass_flow.f_cv],
[rowl.rs2a_flow.f_cv],
[rowl.rs2a_pump4_flow.f_cv],
[rowl.rs2a_station_flow.f_cv],
[rowl.rs4__9_p1_flow.f_cv],
[rowl.rs4__9_p2_flow.f_cv],
[rowl.rs4__9_p3_flow.f_cv],
[rowl.sntsv1_flow.f_cv],
[rowl.sntsv1_flow_spt.f_cv],
[rowl.sntsv2_flow.f_cv],
[rowl.sntsv2_flow_spt.f_cv],
[rowl.sntsv3_flow.f_cv],
[rowl.sntsv3_flow_spt.f_cv],
[rowl.wbs_flow.f_cv],
[rowl.wbs_prs_v1_flow.f_cv],
[rowl.wbs_prs_v2_flow.f_cv],
[rowl.well1_flow.f_cv],
[rowl.well1p2_flow.f_cv],
[rowl.well1p3_flow.f_cv],
[rowl.zone6_flow.f_cv],
[rowl.zonev_flow.f_cv],
[rowl.jl_vlv1_cur_flw.f_cv],
[scadarowd_flow Data];

//Num([scadarowd_flow] Data) as productiontotal;

[vinieme12]

Trim any blank spaces

, check the Len([rowl.wbs_flow.f_cv]) to verify if there are any garbage characters 

NUM(Trim([rowl.wbs_flow.f_cv])) as [rowl.wbs_flow.f_cv]

Crosstable doesn't change the data type,

can you check the data type in source table?

If it's not int or float/decimal at source then try to convert it  during loading

[tanmayNobel]

My source data is text. I wanted to convert it to NUM in qlik. Can I convert the generated  crosstable to NUM?

[vinieme12]

Convert as below 

CROSSTABLE ([scadarowd_flow Tags],[scadarowd_flow Data],2)

LOAD

Field1

,Field2 

,NUM(Trim( field3)) as field3 

NUM(Trim( field4)) as field4

and so on

Or else 

Convert it in your SQL statement

SQL select Field1,field2,cast(field3 as int) as field3,cast(field4 as int) as field4 

This is just an example, lookup the documentation of your database to convert datatype from string to int/decimal