topic Add leading zeros during SQL Load in QlikView

Add leading zeros during SQL Load

gfisch13 — Sat, 16 Nov 2024 01:53:09 GMT

Hi - I'm loading data from an SQL database. The Employee ID field is 7 digits long and some values are dropping the leading zeros.

I'm trying to add them back in my load script using: left(num(CCC_EID,'0000000'),7) as CC_EID but I'm getting error messages that num is not a valid SQL Function.

What could I use in its place?

Any help is appreciated, the more I read the more confused I'm becoming.

Thanks! George

Re: Add leading zeros during SQL Load

Miguel_Angel_Baeyens — Fri, 22 Nov 2019 09:49:33 GMT

Num() might be a valid SQL function for certain RDMBS, however by the looks of it, you are using QlikView syntax on the SQL query, and that breaks.

So do instead something like:

LOAD *,

Text(Left(Num(CCC_EID, '0000000'), 7)) AS CC_EID_2 // the "_2" to avoid duplicate name fields

;

SQL SELECT ... // the SQL query goes here

FROM ...

;

Re: Add leading zeros during SQL Load

gfisch13 — Fri, 22 Nov 2019 13:37:45 GMT

Miguel - thanks for the response. Based on the load script I've already created I'm even more confused on where to place this line of code. Please see my section of load script attached below, I've shortened the script to remove unneeded noise for this topic. I tried exactly what you stated but immediately got an error. Also I've updated the line of code to fit this section...........

Text(Left(Num(EMPE_ID, '0000000'), 7)) AS EE_EMPE_ID

Appreciate your help!

Re: Add leading zeros during SQL Load

Brett_Bleess — Thu, 05 Dec 2019 16:59:59 GMT

Hey George, have a look at the following Help link, I think it might help you, you have a Load statement already, but that is against a specific data source, you can also use Load statement ahead of your SQL Select in the table before that, which is where I think you want to put things in this case:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/preceding-load.htm

Here is a Design Blog post as well that may be helpful too:

https://community.qlik.com/t5/Qlik-Design-Blog/Preceding-Load/ba-p/1469534

Hopefully this will get you what you need to get things working, the expression should be good as far as I can tell too!

Cheers,
Brett

Re: Add leading zeros during SQL Load

gfisch13 — Thu, 05 Dec 2019 19:04:56 GMT

Hi Brett - this is great information on a topic that I've been very confused with. I appreciate the follow up. The expression above did work, but, this will help me understand the 'why' things happen.

Thanks again!