topic Re: If condition in Load Statement in QlikView

If condition in Load Statement

bhuprakash — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: If condition in Load Statement

YoussefBelloum — Thu, 14 Dec 2017 11:00:20 GMT

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 ;

Re: If condition in Load Statement

stabben23 — Thu, 14 Dec 2017 11:05:41 GMT

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,

Re: If condition in Load Statement

stabben23 — Thu, 14 Dec 2017 11:09:45 GMT

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

Re: If condition in Load Statement

devarasu07 — Thu, 14 Dec 2017 11:11:31 GMT

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 & Exists – Useful Functions

Re: If condition in Load Statement

YoussefBelloum — Thu, 14 Dec 2017 11:11:32 GMT

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

Re: If condition in Load Statement

stabben23 — Thu, 14 Dec 2017 11:21:47 GMT

True, User_Days should not be in the Group By.

Group By emp_id, Total

Re: If condition in Load Statement

stabben23 — Thu, 14 Dec 2017 11:24:31 GMT

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

Re: If condition in Load Statement

bhuprakash — Thu, 14 Dec 2017 11:52:06 GMT

Hi Deva,

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

Re: If condition in Load Statement

bhuprakash — Thu, 14 Dec 2017 11:55:49 GMT

Hi Staffan,

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

Re: If condition in Load Statement

Anil_Babu_Samineni — Thu, 14 Dec 2017 11:58:14 GMT

Could be issue here?

Re: If condition in Load Statement

bhuprakash — Thu, 14 Dec 2017 11:58:50 GMT

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);

Re: If condition in Load Statement

stabben23 — Thu, 14 Dec 2017 11:59:23 GMT

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

Re: If condition in Load Statement

stabben23 — Thu, 14 Dec 2017 12:01:31 GMT

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

Re: If condition in Load Statement

bhuprakash — Thu, 14 Dec 2017 12:14:17 GMT

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;

Re: If condition in Load Statement

bhuprakash — Thu, 14 Dec 2017 12:19:05 GMT

Sorry Staffen,

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

Thanks all for help