New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Contributor

if condition with group by return errors

Hi

Why it return invalid expression

count(DISTINCT Date) as DateCount

if(count(DISTINCT Date) < GreenLimit,'GF',

if(count(DISTINCT Date) <= YellowMin and count(DISTINCT Date) >= YellowMax , 'YF',

if(count(DISTINCT Date) >= GreenLimit, 'RF','Null'))) as KPI_Flag

Resident Fact Group by JobID;

Tags (3)
1 Solution

Accepted Solutions
Valued Contributor III

Re: if condition with group by return errors

Trust that you are doing good!

Table:

COUNT(DISTINCT Date) AS DateCount,

If(COUNT(DISTINCT Date) < GreenLimit,

'GF',

If(COUNT(DISTINCT Date) <= YellowMin AND COUNT(DISTINCT Date) >= YellowMax ,

'YF',

If(COUNT(DISTINCT Date) >= GreenLimit,

'RF',

'Null'))) AS KPI_Flag

Resident Fact

Group By

JobID,

GreenLimit,

YellowMin,

YellowMax;

// OR

Table:

DateCount,

If(DateCount < GreenLimit,

'GF',

If(DateCount <= YellowMin AND DateCount >= YellowMax ,

'YF',

If(DateCount >= GreenLimit,

'RF',

'Null'))) AS KPI_Flag;

GreenLimit,

YellowMin,

YellowMax,

COUNT(DISTINCT Date) AS DateCount

Resident Fact

Group By

JobID,

GreenLimit,

YellowMin,

YellowMax;

P.S.: You have also missed to add a comma ( i.e. ,) after DateCount field.

Regards!

Rahul

2 Replies
MVP

Re: if condition with group by return errors

All fields have to be either used with an aggregation function or part of the group by clause:

hope this helps

regards

Marco

Valued Contributor III

Re: if condition with group by return errors

Trust that you are doing good!

Table:

COUNT(DISTINCT Date) AS DateCount,

If(COUNT(DISTINCT Date) < GreenLimit,

'GF',

If(COUNT(DISTINCT Date) <= YellowMin AND COUNT(DISTINCT Date) >= YellowMax ,

'YF',

If(COUNT(DISTINCT Date) >= GreenLimit,

'RF',

'Null'))) AS KPI_Flag

Resident Fact

Group By

JobID,

GreenLimit,

YellowMin,

YellowMax;

// OR

Table:

DateCount,

If(DateCount < GreenLimit,

'GF',

If(DateCount <= YellowMin AND DateCount >= YellowMax ,

'YF',

If(DateCount >= GreenLimit,

'RF',

'Null'))) AS KPI_Flag;

GreenLimit,

YellowMin,

YellowMax,

COUNT(DISTINCT Date) AS DateCount

Resident Fact

Group By

JobID,

GreenLimit,

YellowMin,

YellowMax;

P.S.: You have also missed to add a comma ( i.e. ,) after DateCount field.

Regards!

Rahul