topic Re: Link fields of two different table in set analysis in QlikView

Link fields of two different table in set analysis

Wed, 16 Oct 2013 12:04:17 GMT

Hello,

I have a Calendar table and a Attendance table. I am not linking the Calendardate and the Attendance date in the two tables. The two tables are not linked. Now I have a chart where I am taking CalMonth as dimension and I want sume of Hrs worked for that Cal Month.

The expression I have written is taking a lots of time., so I am trying to replace the if by using set analysis. I have written an expression as :

Count(Distinct {$<EmpStatus = {'A'}>}if(CalMonth=AttMonth and CalYear=AttYear,
if( Aggr(Sum({$<EmpStatus = {'A'},ATTWorkFlag = {'Working'}>} if(CalMonth=AttMonth and CalYear=AttYear,HrsWorkSecs)), EmpID,CalMonth,CalendarDate) >=
Aggr(Sum({$<EmpStatus = {'A'},WorkFlag = {'Working'}>} $(MinWorkHrsSecs)),

EmpID,

CalMonth)
,

EmpID)))

When I am trying to use set analysis its not giving me any output... Please help!!!

Re: Link fields of two different table in set analysis

Wed, 16 Oct 2013 12:08:53 GMT

Hi,

Try sth like that in your set:

{< ..... CalMonth = {$(=concat(AttMonth ',')} >}

assuming that CalMonth and AttMonth have the same key values

Fabrice

Re: Link fields of two different table in set analysis

Wed, 16 Oct 2013 12:19:39 GMT

Thanks for ur solution,

I tried like this., but its not giving any output....

{$<EmpStatus = {'A'},CalMonth = {"=AttMonth"},CalYear = {"=AttYear"}>}

Re: Link fields of two different table in set analysis

Wed, 16 Oct 2013 12:21:20 GMT

it is why you should use the concat function, to transfor the value into text

Fabrice

Re: Link fields of two different table in set analysis

Fri, 20 Feb 2015 06:54:24 GMT

Hello Fabrice,

Thanks for your help. But the Concat didnt work. Could you think of any other solution...