topic Re: Qlikview version of sql . in QlikView

Qlikview version of sql .

Tue, 10 Dec 2013 16:32:13 GMT

What is the qlikview version of this SQL server query ?

CAST(Company.Company_Id AS INT) AS REL_ID

Thanks a lot.

Re: Qlikview version of sql .

Anonymous — Tue, 10 Dec 2013 16:37:25 GMT

num(Company.Company_Id,'#,##0') as REL_ID

Re: Qlikview version of sql .

Tue, 10 Dec 2013 18:17:20 GMT

This is making my REL_ID to 0.

after num(Compnay.Company_ID,'#,##0') ,0000000000000F42 should become 3906,but it is displaying 0.

Re: Qlikview version of sql .

Anonymous — Tue, 10 Dec 2013 18:28:29 GMT

Good to know it is hex...

This should work:

num(num#(right(Company.Company_Id,15),'(HEX)')) as REL_ID

But I had to cut the length a little using right() for the last 15 digits. QlikView can't use numbers longer than 15 digits. Try this, it returns 3906:

=num(num#(right('0000000000000F42',15),'(HEX)'))

Re: Qlikview version of sql .

Tue, 10 Dec 2013 18:34:09 GMT

YES !! It did work Thanks a bumch.

Re: Qlikview version of sql .

Wed, 11 Dec 2013 18:51:29 GMT

Michael,

I have an account number field with account number 00508166730000000098.

How can I conver this account number into 50681376-42 ?

In Oracle I know I can dod it using ltrim and substring function.

LTRIM(SUBSTR(ACCOUNT_NO,3,8),0) || '-' || LTRIM(SUBSTR(ACCOUNT_NO,11),0).

If you find some time please help me with this.

Thanks a lot for your time.

Re: Qlikview version of sql .

Anonymous — Wed, 11 Dec 2013 19:07:42 GMT

I guess you want this:

=num(mid(ACCOUNT_NO,3,8)) & '-' & num(mid(ACCOUNT_NO,11))

Although I'm not sure because the result is a little different for your data (00508166730000000098😞

50816673-98

Re: Qlikview version of sql .

Wed, 11 Dec 2013 19:16:49 GMT

As Saidby Michael we use

=num(mid(ACCOUNT_NO,3,8)) & '-' & num(mid(ACCOUNT_NO,11))

for ocnversions, but the number it should return is some what different, could you recheck the question?

Re: Qlikview version of sql .

Wed, 11 Dec 2013 19:23:13 GMT

Thank you Michael.You are absolutely right.It was my bad.I used the wrong output in my question.

Re: Qlikview version of sql .

Wed, 11 Dec 2013 19:23:50 GMT

Thank you Gayathri for the heads up.It was my bad.