topic Re: Expression within set analysis in QlikView

Expression within set analysis

Thu, 16 Apr 2015 15:07:52 GMT

Hello,

I'm new to the forum. I wonder if anybody can help me....

Essentially, in pseudo code I'm looking to create an expression that can does the below:

min (

if (

Count({<[Location] = {'At work'}>} People) < Count(({<[Location] = {'Not at work'}>} People), Time, Nothing

)

so basically, in my data, there will be many times across the day when the number of people 'Not at work' is greater than those 'At work', but I want to return the minimum time this happens.

This expression will be put in a straight table chart where the Dimension is DayofWeek. So essentially I want to find out the minimum time when 'Not at work' > 'At work' per day.....

any thoughts or suggestions would be much appreciated!

Colin

Re: Expression within set analysis

robert_mika — Thu, 16 Apr 2015 15:14:14 GMT

Could you post sample of your data?

Re: Expression within set analysis

Thu, 16 Apr 2015 15:24:56 GMT

Unfortunately I cant post the data in its current format, but can work on creating a sample doc to share.

An abbreviated data structure could be shown as:

People Timeslot Day Location

A 1 1 At Work

A 2 1 At Work

A 3 1 Not At Work

A 4 1 At Work

B 1 1 Not At Work

B 2 1 Not At Work

B 3 1 Not At Work

B 4 1 Not At Work

C 1 1 At Work

C 2 1 At Work

C 3 1 Not At Work

C 4 1 Not at Work

D 1 1 At Work

D 2 1 At Work

D 3 1 Not At Work

D 4 1 Not At Work

So for Day 1,

Timeslot 1 has 3 people at work and 1 not.

Timeslot 2 has 3 people at work and 1 not

Timeslot 3 has 4 people not at work.

Timeslot 4 has 4 people not at work

I would want the expression to output '3' for Day1 etc as its the minimum timeslot where count({Not At Work} People) > Count({At Work} People)

Hope that helps?

Re: Expression within set analysis

Eugeny_Ilyin — Fri, 17 Apr 2015 05:56:24 GMT

Hi,

script example:

tmp1:
 LOAD * INLINE [
People, Timeslot, Day, Location
A,1,1,At Work
A,2,1,At Work
A,3,1,Not At Work
A,4,1,At Work
B,1,1,Not At Work
B,2,1,Not At Work
B,3,1,Not At Work
B,4,1,Not At Work
C,1,1,At Work
C,2,1,At Work    
C,3,1,Not At Work
C,4,1,Not at Work
D,1,1,At Work              
D,2,1,At Work
D,3,1,Not At Work    
D,4,1,Not At Work              
];

temp1:
LOAD Distinct Day, Timeslot Resident tmp1;


temp2:
left join (temp1) load Day, Timeslot, count(People) as NAW
Resident tmp1 
where Location='Not At Work'
Group by Day,Timeslot ;

left join (temp1) load Day, Timeslot, count(People) as AW
Resident tmp1
where Location='At Work'
Group by Day,Timeslot ;

DROP Table tmp1;

temp3:
load Day, min(Timeslot) as MinTimeslot
Resident temp1
where (if(NAW>0,NAW,0)-if(AW>0,AW,0))>0
group by Day;

DROP Table temp1;