Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
My source data is text. I wanted to convert it to NUM in qlik. Can I convert the generated crosstable to NUM?
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