Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Nov. 30th, Webinar! Do More With Qlik Cloud Catalog & Lineage: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
tanmayNobel
Contributor
Contributor

Crosstable load does not load the data as Num.

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;

Labels (2)
3 Replies
vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
tanmayNobel
Contributor
Contributor
Author

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

vinieme12
Champion III
Champion III

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

 

 

 

 

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.