Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
Try left([Account Number],9) instead of mid()?
Otherwise your expression looks OK to me
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
Thank you colin.
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 ?
Hi aptha0115,
Try this function,
=Right(098765432,8)
- Regards,
Vishal Waghole
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
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;
Hi,
Try this
Round(Acc_No)