Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Could be issue here?
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);
As i Said, Total is not a Field in activity_master table,
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
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;
Sorry Staffen,
It is working fine. I had some confusion but now it is clear.
Thanks all for help