topic Re: How can I write this sql in qlikview.Please help me. in QlikView

How can I write this sql in qlikview.Please help me.

Wed, 18 Dec 2013 17:41:38 GMT

WHEN (ITEM_ID = 'LR_R6R7_I' OR

ITEM_ID = 'L_R6R7_I' OR

ITEM_ID = 'L_NRR6R7_I')

THEN SUBSTR(ROI_NBR_SESSIONS_2013.ACCOUNT_NO,1,9)

ELSE ACCOUNT_NO END AS InSite_Acct,

I tried

if(match([Account Number],'LR_R6R7_I','L_R6R7_I','L_NRR6R7_I'), mid([Account Number],1,9), [Account Number]) as INsite_Acct

After mid() function [Account Number]=JX6012734-53422e439 SHOULD BECOME

INsite_Acct= JX6012734

Re: How can I write this sql in qlikview.Please help me.

Gysbert_Wassenaar — Wed, 18 Dec 2013 17:46:05 GMT

Shouldn't that match bit compare ITEM_ID instead of Account Number like it does in your sql expression?

match(ITEM_ID,'LR_R6R7_I','L_R6R7_I','L_NRR6R7_I')

Mid([Account Number],1,9) looks correct. Left([Account Number],9) should work too. Or subfield([Account Number],'-',1)

Re: How can I write this sql in qlikview.Please help me.

Wed, 18 Dec 2013 17:47:13 GMT

Try left([Account Number],9) instead of mid()?

Otherwise your expression looks OK to me

Re: How can I write this sql in qlikview.Please help me.

Colin-Albert — Wed, 18 Dec 2013 17:47:26 GMT

Try this

IF(WILDMATCH(ITEM_ID,'LR_R6R7_I*','L_R6R7_I*','L_NRR6R7_I*') > 0,

LEFT(ROI_NBR_SESSIONS_2013.ACCOUNT_NO,9),

ACCOUNT_NO ) as InSite_Acct

Re: How can I write this sql in qlikview.Please help me.

Wed, 18 Dec 2013 18:04:55 GMT

Thank you colin.

Re: How can I write this sql in qlikview.Please help me.

Wed, 18 Dec 2013 18:09:05 GMT

Colin,

I have an account number like 098765432

I want to trim the 0 and want to show 98765432.

I tried , ltrim([Account Number],0)

It is not working.

Which function can I use ?Can u please help me ?

Re: How can I write this sql in qlikview.Please help me.

VishalWaghole — Wed, 18 Dec 2013 18:21:29 GMT

Hi aptha0115,

Try this function,

=Right(098765432,8)

- Regards,

Vishal Waghole

Re: How can I write this sql in qlikview.Please help me.

Colin-Albert — Wed, 18 Dec 2013 23:27:26 GMT

If the account number is a numeric string then you can remove leading zeros by using

num([Account Number],'#########') as [Account Number]

You may also want to add a text() function around the expression which will tell QlikView to treat the account number as a text value. Checked this is consistent across other tables if the account number is a key field though.

The ltrim command strips leading spaces from the field and does not remove zeros

Re: How can I write this sql in qlikview.Please help me.

Thu, 19 Dec 2013 03:42:27 GMT

Hi Aptha,

Try this

LOAD Trim(Acc_No) as TrimAcc Resident No;

LOAD LTrim(Acc_No) as LtrimAcc Resident No;

LOAD RTrim(Acc_No) as RtrimAcc Resident No;

LOAD PurgeChar(Acc_No,'0') as PurgAcc Resident No;

LOAD Right(Acc_No,8) as RightAcc Resident No;

LOAD Left(Acc_No,9) as LightAcc Resident No;

LOAD Num(Acc_No,'#########.##') as NumFormAc Resident No;

Re: How can I write this sql in qlikview.Please help me.

Thu, 19 Dec 2013 03:58:41 GMT

Hi,

Try this

Round(Acc_No)