If condition in Load Statement

[bhuprakash]

Dear All,

I am using below query to get slot wise data based on user days but query is giving error. Please help me in the same.

LOAD emp_id,count(DISTINCT Activity_Date) as User_Days,

if( User_Days > 25, '>25',

      If(User_Days > 20, '21-25',

      if(User_Days > 10, '11-20',

      if(User_Days > 0, '1-10', '0')))) as Bucket,

     [Total]/25 as [Avg Reporting per day],  

     if( [Total]/25 > 3, 'More than 3',

      if( [Total]/25 = 3, '3',

      if( [Total]/25 > 0, 'Below 3', '0'))) as [Avg reporting per day bucket]

RESIDENT activity_master where Activity_Date > 0 Group By emp_id;

[YoussefBelloum]

Hi,

you are loading from a resident table "activity_master "

Be sure that the field "User_Days" is not renamed on the "activity_master " table.

and try this:

LOAD emp_id,count(DISTINCT Activity_Date) as User_Days,

if( User_Days > 25, '>25',

      If(User_Days > 20, '21-25',

      if(User_Days > 10, '11-20',

      if(User_Days > 0, '1-10', '0')))) as Bucket,

     [Total]/25 as [Avg Reporting per day],

     if( [Total]/25 > 3, 'More than 3',

      if( [Total]/25 = 3, '3',

      if( [Total]/25 > 0, 'Below 3', '0'))) as [Avg reporting per day bucket]

RESIDENT activity_master where Activity_Date > 0

Group By emp_id,User_Days, Total ;

[stabben23]

Hi,

User_Days is a renamed Field in Your table, it could not be used here, use Activity_Date.

if( Activity_Date> 25, '>25',

      If(Activity_Date> 20, '21-25',

      if(Activity_Date> 10, '11-20',

      if(Activity_Date> 0, '1-10', '0')))) as Bucket,

[stabben23]

you could also do a precedingLOAD

Load *,

if( User_Days > 25, '>25',

      If(User_Days > 20, '21-25',

      if(User_Days > 10, '11-20',

      if(User_Days > 0, '1-10', '0')))) as Bucket

;

LOAD emp_id,count(DISTINCT Activity_Date) as User_Days,

   [Total]/25 as [Avg Reporting per day],  

     if( [Total]/25 > 3, 'More than 3',

      if( [Total]/25 = 3, '3',

      if( [Total]/25 > 0, 'Below 3', '0'))) as [Avg reporting per day bucket]

RESIDENT activity_master where Activity_Date > 0 Group By emp_id;/servlet/JiveServlet/downloadImage/2-1401234-186975/error.png

[devarasu07]

Hi,

Try like this preceding load method (nested calculated can be done this way)

load *,

if( User_Days >25, '>25',

      If(User_Days >=21 and User_Days<=25, '21-25',

      if(User_Days >=11 and User_Days <=20, '11-20',

      if(User_Days >0 and User_Days <=10, '1-10', '0')))) as Bucket,   

     if( [Total]/25 > 3, 'More than 3',

      if( [Total]/25 = 3, '3',

      if( [Total]/25 <=3 0, 'Below 3', '0'))) as [Avg reporting per day bucket];

LOAD emp_id,

count(DISTINCT Activity_Date) as User_Days,

[Total]/25 as [Avg Reporting per day],

RESIDENT activity_master where count(DISTINCT Activity_Date)>0 Group By emp_id;

also check this post,

Dual &amp; Exists – Useful Functions

[YoussefBelloum]

on his Group by, shouldn't he add all the fields that don't contain an aggregation function ?

[stabben23]

True, User_Days should not be in the Group By.

Group By emp_id, Total

[stabben23]

That also true, for ex 26 will be >0, >10, >20, >25, and the nested if will fail.

[bhuprakash]

Hi Deva,

While i am using your query then it is giving error. Please check.

[bhuprakash]

Hi Staffan,

I am getting below error while using your query. Please help me in the same.