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

[#1]

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

[Gysbert_Wassenaar]

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)

[#3]

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

Otherwise your expression looks OK to me

[Colin-Albert · Accepted solution]

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

[#5]

Thank you colin.

[#6]

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 ?

[VishalWaghole]

Hi aptha0115,

Try this function,

=Right(098765432,8)

- Regards,

Vishal Waghole

[Colin-Albert]

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

[#9]

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;

[#10]

Hi,

Try this

Round(Acc_No)