topic Re: if Statement in QlikView

if Statement

priyarane — Fri, 20 Mar 2015 08:33:53 GMT

Hi Expets,

How to write the below expression in our Script part.

First(IIf([Template]="Retail",[BaselII_Category] & " Pooled",[Cust_Name]))
If Cust Name is Blank,Null then Use Cust ID. If Cust ID is also blannk use Basel Category

Thank you

Re: if Statement

Gysbert_Wassenaar — Fri, 20 Mar 2015 09:00:14 GMT

First(IIf([Template]="Retail",[BaselII_Category] & " Pooled",[Cust_Name]))

if(Template='Retail',BaselII_Category & ' Pooled',Cust_Name)

No idea what your First does, so I don't know what the Qlikview equivalent is.

If Cust Name is Blank,Null then Use Cust ID. If Cust ID is also blannk use Basel Category

if(len(trim([Cust Name]))=0, if(len(trim([Cust ID]))=0,BaselII_Category,[Cust ID]),[Cust Name])

Re: if Statement

Peter_Cammaert — Fri, 20 Mar 2015 09:05:32 GMT

I think there are not one but two expressions in your quote.

This looks like an MS-Access expression. Try:
FIRST 1 LOAD IF ([Template] = 'Retail', [BaselII_Category] & ' Pooled', [Cust Name]) AS NewColumn FROM ...;
This looks like a requirement in plain English. Try this (replace names with the actual column names):
IF (len(trim([Cust Name])) = 0, if (len(trim([Cust ID])) = 0, [Basel Category], [Cust ID]), [Cust Name]) AS [Cust Name], ...

Best,

Peter

Re: if Statement

Peter_Cammaert — Fri, 20 Mar 2015 09:07:29 GMT

In MS-Acces, First() loads the first value in a table column. I didn't know that you could embed IF expressions as a parameter.

Re: if Statement

Gysbert_Wassenaar — Fri, 20 Mar 2015 09:09:04 GMT

Sounds like an aggregation function like FirstValue.

Re: if Statement

mukesh24 — Fri, 20 Mar 2015 09:09:24 GMT

Can u please explain u r first condition --> First(IIf([Template]="Retail",[BaselII_Category] & " Pooled",[Cust_Name]))

for second u can try -->

if(Isnull([Cust Name])=-1, if(isnull([Cust ID]) =-1, [Basel Category],[Cust ID])

Re: if Statement

Peter_Cammaert — Fri, 20 Mar 2015 09:11:58 GMT

Could be, but the entire context is missing from the OP. A simple FIRST 1 prefix may be a solution too.

Re: if Statement

Gysbert_Wassenaar — Fri, 20 Mar 2015 09:13:15 GMT

Indeed.

Re: if Statement

Fri, 20 Mar 2015 09:27:55 GMT

Hi,

You can try this expression.

=First(If([Template]="Retail",[BaselII_Category] ,"Pooled"),[Cust_Name])

Re: if Statement

priyarane — Fri, 20 Mar 2015 09:35:42 GMT

Right now we can leave about First.

Can I write expression like this:

(Template = 'Retail' ,BaselII_Category &'-'& 'Pooled',
if(len(trim(Cust_Name))=0 or IsNull(Cust_Name) = -1,CIF_Cust_ID,if(len(trim(CIF_Cust_ID))=0 or IsNull(CIF_Cust_ID) = -1,BaselII_Category))) as Cust_Name,

Re: if Statement

priyarane — Fri, 20 Mar 2015 09:36:27 GMT

Right now we can leave about First.

Can I write expression like this:

Re: if Statement

priyarane — Fri, 20 Mar 2015 09:38:03 GMT

Hi Gysbert,

Right now we can leave about First.

Can I write expression like this:

Re: if Statement

priyarane — Fri, 20 Mar 2015 09:44:58 GMT

Hi Peter, Can I go like below:

if(Template = 'Retail' ,BaselII_Category &'-'& 'Pooled',
if(len(trim(Cust_Name))=0 or IsNull(Cust_Name) = -1,CIF_Cust_ID,if(len(trim(CIF_Cust_ID))=0 or IsNull(CIF_Cust_ID) = -1,BaselII_Category))) as Cust_Name,

Re: if Statement

Gysbert_Wassenaar — Fri, 20 Mar 2015 09:53:13 GMT

The isnull() function isn't necessary. Len(trim(...)) is also 0 for null values.

if (Template = 'Retail' ,BaselII_Category &'-'& 'Pooled',

if(len(trim(Cust_Name))=0,

if(len(trim(CIF_Cust_ID))=0, BaselII_Category, CIF_Cust_ID),Cust_Name)) as Cust_Name,

Re: if Statement

Peter_Cammaert — Fri, 20 Mar 2015 10:00:51 GMT

Yes, you can but IMHO it will not produce the results you expect. For example, if [Cust_Name] contains data, it may end up being changed into a NULL value. Two points:

Drop every IsNull() = -1 logical expression. The trick with len(trim()) handles NULL values too.
Consider this pseudo-code representation of your entire IF expression. I added a default NULL-value. Is this correct?
IF Template = Retail
THEN Cust_Name = BaselII_Category & '-' & 'Pooled'
ELSE
IF Cust_Name is NULL or empty string
THEN Cust_Name = CIF_Cust_ID
ELSE
    IF CIF_Cust_ID is NULL or empty string
    THEN Cust_Name = BaselII_Category
    ELSE Cust_Name = NULL

Best,

Peter