Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
vengadeshpalani
Contributor

if condition with group by return errors

Hi

Why it return invalid expression

load JobID,

       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;

1 Solution

Accepted Solutions
rahulpawarb
Valued Contributor III

Re: if condition with group by return errors

Hello Vengadesh,

Trust that you are doing good!

Please refer below sample scripts:

Table:

load JobID,

     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:

LOAD JobID,

      DateCount,

   If(DateCount < GreenLimit,

      'GF',

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

     'YF',

            If(DateCount >= GreenLimit,

    'RF',

    'Null'))) AS KPI_Flag;

LOAD JobID,

      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

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:

Load

hope this helps

regards

Marco

rahulpawarb
Valued Contributor III

Re: if condition with group by return errors

Hello Vengadesh,

Trust that you are doing good!

Please refer below sample scripts:

Table:

load JobID,

     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:

LOAD JobID,

      DateCount,

   If(DateCount < GreenLimit,

      'GF',

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

     'YF',

            If(DateCount >= GreenLimit,

    'RF',

    'Null'))) AS KPI_Flag;

LOAD JobID,

      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