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: 
yelin_nyu
Creator
Creator

text format

Hi,

I did something to ID, and it's returning as number instead of string. My tbls won't get to join properly because things didn't match.

tblX:

sql select

left(right(ID, 5), 4)+ '0' as NID

from xxx.qvd;

left join (tblX)

sql select left(PIN, 9) +'0' as NID

FROM tbl123;

i want to make sure NID is a 5 digit code with ending equal to 0.

ie. '00050' should not be 5

1 Solution

Accepted Solutions
MayilVahanan

Hi

PFA

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

7 Replies
MayilVahanan

HI

Use

text() function

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
yelin_nyu
Creator
Creator
Author

hi,

i tried

tblX:

sql select

text(left(right(ID, 5), 4)+ '0') as NID

from xxx.qvd;

left join (tblX)

sql select text(left(PIN, 5) +'0') as NID

FROM tbl123;

but didn't work

'00050' is returning as 5, not values pulled from tbl123 because tblX.NID and tbl123.NID do not match

MayilVahanan

HI

Try like this

text(left(right(ID, 5), 4))& '0' as NID

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Anonymous
Not applicable

use & instead

text(left(right(ID, 5), 4)+ '0') as NID => left(right(ID, 5), 4) & '0' as NID

CELAMBARASAN
Partner - Champion
Partner - Champion

As per your logic 5 digit will be equal to 9 digit? how it is possible?

Then you should use & instead of + here.

I may misunderstood can you upload a sample file?

yelin_nyu
Creator
Creator
Author

typo.... it's 5

tblX:

sql select

left(right(ID, 5), 4)+ '0' as NID

from xxx.qvd;

left join (tblX)

sql select left(PIN, 5) +'0' as NID

FROM tbl123;

MayilVahanan

Hi

PFA

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.