topic Re: text format in QlikView

text format

yelin_nyu — Wed, 14 Nov 2012 21:15:14 GMT

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

Re: text format

MayilVahanan — Wed, 14 Nov 2012 21:19:23 GMT

Use

text() function

Hope it helps

Re: text format

yelin_nyu — Wed, 14 Nov 2012 21:21:25 GMT

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

Re: text format

MayilVahanan — Wed, 14 Nov 2012 21:22:47 GMT

Try like this

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

Hope it helps

Re: text format

Anonymous — Wed, 14 Nov 2012 21:23:04 GMT

use & instead

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

Re: text format

CELAMBARASAN — Wed, 14 Nov 2012 21:23:04 GMT

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?

Re: text format

yelin_nyu — Wed, 14 Nov 2012 21:25:10 GMT

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;

Re: text format

MayilVahanan — Wed, 14 Nov 2012 21:29:00 GMT

PFA