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;

[Anil_Babu_Samineni]

Could be issue here?

[bhuprakash]

Below is my main insert query

Below

activity_master:

LOAD [Activity Id] as act_id,

     [Activity Type] as act_type,

     [Employee Id] as emp_id,

     Location,

     Locality,

     Pincode,

     [Activity Code],

     [Activity Description],

     [User ActivityCreatedDate],

     [Server ActivityCreatedDate],

     [Activity Created Time],

     [Marketing Territory Code],

     [Marketing Territory],

     PrimaryCrop,

     PrimaryProduct,

     DATE(floor([User ActivityCreatedDate]), 'DD-MM-YYYY') AS Activity_Date

FROM

(ooxml, embedded labels, table is ActivityDetails);

[stabben23]

As i Said, Total is not a Field in activity_master table,

[stabben23]

No Total in here, so you can not reload it in a resident table

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

[bhuprakash]

r correction below query is working but giving one incorrect result. Only Below 3 is working for average_reporting_days.

So please help me to correct this. Rest looking fine.

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,

   count(DISTINCT Activity_Date)/25 as average_reporting, 

     if( count(DISTINCT Activity_Date)/25 > 3, 'More than 3',

      if( count(DISTINCT Activity_Date)/25 = 3, '3',

      if( count(DISTINCT Activity_Date)/25 > 0, 'Below 3', '0'))) as average_reporting_days

RESIDENT activity_master where Activity_Date > 0 Group By emp_id;

[bhuprakash · Accepted solution]

Sorry Staffen,

It is working fine. I had some confusion but now it is clear.

Thanks all for help