Add leading zeros during SQL Load

[gfisch13]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

;

[gfisch13]

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!

[Brett_Bleess]

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/prece...

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

[gfisch13]

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!