Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vengadeshpalani
Creator
Creator

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
Specialist III
Specialist III

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

View solution in original post

2 Replies
MarcoWedel

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
Specialist III
Specialist III

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