topic Re: SQL Load Script - Change Data Type in App Development

SQL Load Script - Change Data Type

mskusace — Wed, 22 Dec 2021 20:32:06 GMT

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]

Re: SQL Load Script - Change Data Type

Marcos_rv — Wed, 17 Jul 2019 15:59:23 GMT

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!!!

Re: SQL Load Script - Change Data Type

mskusace — Wed, 17 Jul 2019 17:16:18 GMT

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?

Re: SQL Load Script - Change Data Type

mskusace — Wed, 17 Jul 2019 17:17:05 GMT

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.

Re: SQL Load Script - Change Data Type

Marcos_rv — Wed, 17 Jul 2019 17:42:36 GMT

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

Re: SQL Load Script - Change Data Type

mskusace — Wed, 17 Jul 2019 17:47:03 GMT

Would something like this work:

LIB CONNECT TO 'DATABASE';

TABLE_FINAL

LOAD

TEXT(NAME) AS NAME;

SQL SELECT

NAME,

AGE,

TEXT

FROM TABLE.THING

Re: SQL Load Script - Change Data Type

Marcos_rv — Wed, 17 Jul 2019 18:06:08 GMT

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!!!!

Re: SQL Load Script - Change Data Type

mskusace — Wed, 17 Jul 2019 18:50:20 GMT

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!