Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gfisch13
Creator II
Creator II

Add leading zeros during SQL Load

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

Labels (2)
1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

;

View solution in original post

4 Replies
Miguel_Angel_Baeyens

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
Creator II
Creator II
Author

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
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
gfisch13
Creator II
Creator II
Author

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!