Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mskusace
Creator
Creator

SQL Load Script - Change Data Type

I have a SQL query that I developed outside of Qlik and am using in the data load editor to pull my data through a data connection. It is a direct copy/paste. I load the connection using "LIB CONNECT TO 'DATABASE'; command. One of my fields needs to be displayed as a TEXT function, but some of the values are being read as a number so my trailing 0's are getting dropped off. How do I convert the data type from a SQL load to be text only?

 

LIB CONNECT TO 'DATABASE';

 SQL SELECT

[CODE]

 

Labels (2)
7 Replies
Marcos_rv
Creator II
Creator II

try this :

//Use text(), num() and preceding LOAD. 

LIB CONNECT TO 'DATABASE';

 

TABLE_FINAL:

LOAD

TEXT(NAME) AS NAME,

NUM(AGE) AS AGE,

TEXT(TEXT) AS TEXT,

NUM(NUM) AS NUM

;

 SQL SELECT

NAME,

AGE,

TEXT,

NUM

FROM TABLE.THING;

 

saludos!!!

mskusace
Creator
Creator
Author

I appreciate the reply. Do I need to load ALL the fields in the table or can I just load the ones I need to use?

mskusace
Creator
Creator
Author

I'm using data from about 6 tables so it ends up being a lot of LOAD statements and fields. That was the intention of me asking.

Marcos_rv
Creator II
Creator II

you should use that string transformation only in the fields you need, it is not necessary that they all be. regards!!!

mskusace
Creator
Creator
Author

Would something like this work:

LIB CONNECT TO 'DATABASE';

TABLE_FINAL

LOAD

TEXT(NAME) AS NAME;

SQL SELECT

NAME,

AGE,

TEXT

FROM TABLE.THING

Marcos_rv
Creator II
Creator II


nope, you have to declare all the fields, that you are going to use, maybe you do not want to transform the rest but if or if you have to declare them.

LIB CONNECT TO 'DATABASE';
FINAL TABLE
LOAD
TEXT (NAME) AS NAME,
AGE,
TEXT;
SQL SELECT
YAM,
AGE,
TEXT
FROM TABLE.THING

although if in other tables the rest of the fields need transformation it is fine that you only use the sql declaration without the load.

Regards!!!!

mskusace
Creator
Creator
Author

Got it! Thanks. My leading 0s were actually not getting dropped, so I just used the "TEXT" function in my visualization itself to change the data type so it is all good now! I appreciate your assistance!