SQL Load Script - Change Data Type

[mskusace]

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]

[Marcos_rv]

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]

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]

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]

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

[mskusace]

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]

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]

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!