like operator with if condition

[sunil-kumar5]

Hello All,

I need to create a table where I have to separate the column value into two parts one for 'Fixed Deposit' and another for 'Fixed Deposit against BG'. I just need to have a dimension and against that I will put the measures accordingly.

Table

Name	AccountGroupName
FD (134567) BG	Fixed Deposits
FD (134567) BG(EMP)	Fixed Deposits
FD (134567) (EMP) BG	Fixed Deposits
FD (876543)	Fixed Deposits
FD (3456tyg)	Fixed Deposits
23456786(FD)	Fixed Deposits

I have created for 'Fixed Deposit against BG' using  - If(AccountGroupName= 'Fixed Deposits' AND Name like '*BG*',Name) to get the only records that have 'BG' however I am stuck only for 'Fixed Deposit' as if condition is not taking the not like operator.

Please help

Thanks

Sunil Kumar

[AshutoshBhumkar]

Hi, You can try using wildmatch() function in Qlik to compare the string value.

e.g

If(AccountGroupName= 'Fixed Deposits' AND Wildmatch(Name, '*BG*'), Name)

[sunil-kumar5]

Thanks for your reply.

I have already achieved the requirement of Fixed Deposit against BG however I am stuck in Fixed Deposit only where name field does not have BG that means last three records.

[stevejoyce]

Not operator is <> or you can do not(<condition>).

It sounds like you want to do an OR of these two conditions.  But likely I'm not understanding correctly...

If(AccountGroupName= 'Fixed Deposits' OR Wildmatch(Name, '*BG*'), Name)

[sunil-kumar5]

I just want to show last three records of above table which would come using <> or not like with "*BG*" but , when I am using it , I am not getting the last three records

[AshutoshBhumkar]

Ok, then maybe you can use this expression.

=If(AccountGroupName = 'Fixed Deposits' and not WildMatch(Name,'*BG*'), Name)

This will give you the last 3 records which does not have BG in it and pertain to Account group 'Fixed Deposits' .

[Kushal_Chawda]

@sunil-kumar5  how about creating new group field in load script like below

LOAD *,
if(AccountGroupName='Fixed Deposits',
if(Name like '*BG*','Fixed Deposit against BG','Fixed Deposits'),AccountGroupName) as New_group

FOM table;

then you can use New_group to filter out values

[sunil-kumar5 · Accepted solution]

I was able to solve it like :

If(AccountGroupName = 'Fixed Deposits' and WildMatch(Name,'*BG*')=0, Name)