topic Re: Set analysis question vs SQL query in QlikView

Set analysis question vs SQL query

raju_salmon — Wed, 06 Jan 2016 04:49:26 GMT

Hi,

I have a below SQL queries which i am trying to implement in Set analysis.

Question 1:

SQL Field DERIVATION:

[Count]= Count(DISTINCT CASE WHEN callattended = 0 THEN 0 ELSE DATE END)-1

callattended, DATE are fields (callattended VALUE we have 0 and 1)

In set analasys i tried with below one, but giving wrong results.

COUNT(DISTINCT {$<callattended= {1}>} DATE) -1

Question 2:

SQL Field DERIVATION:

[Uptodate]= CASE WHEN SUM(callattended) = 0 THEN 0 ELSE (RC * CEILING(COUNT(DISTINCT Date))/22)

RC is field.

In set analasys i tried with below one, but giving wrong results.

Floor(If(Sum(callattended)=0,0,(RC*Count(DISTINCT Date)/22)))

Can anyone help me on this?

Thanks.

Re: Set analysis question vs SQL query

sujeetsingh — Wed, 06 Jan 2016 04:59:10 GMT

Come with sample please

Re: Set analysis question vs SQL query

jonathandienst — Wed, 06 Jan 2016 05:03:19 GMT

Set expressions o not work in the load script, but assuming you are trying to use this logic in front end expressions:

You don't need the -1 in the first expression, so this should work:

COUNT({$<callattended= {1}>} DISTINCT DATE)

The Floor is redundant in the second expression, but it is otherwise syntactically correct.

If they are still not working, I suggest you post an example qvw file with representative data, that shows how you are using the two expressions - and indicate what results you expect.

Re: Set analysis question vs SQL query

Anonymous — Wed, 06 Jan 2016 05:06:11 GMT

In set analasys i tried with below one, but giving wrong results.

Floor(If(Sum(callattended)=0,0,(RC*Count(DISTINCT Date)/22)))

This is not a Set Analysis, it is simple If condition.....

check this for Set Analysis:

Learning Set Analysis from Scratch

Re: Set analysis question vs SQL query

raju_salmon — Wed, 06 Jan 2016 05:30:08 GMT

I am using these in char expression only. Thanks Jonathan.

Yes, the first one work fine. But i am wonder why we have removed -1 from expression. Since SQL expression clearly showing -1.

Can i you please provide more information on second expression how can we change it to get correct result?

Thanks for your time.

Re: Set analysis question vs SQL query

raju_salmon — Wed, 06 Jan 2016 05:31:06 GMT

Sorry for confusion Balraj. Yes, this is condition, can you please help me how can we change it to get correct results with respect to SQL query.

Thanks.

Re: Set analysis question vs SQL query

jonathandienst — Wed, 06 Jan 2016 05:40:39 GMT

Raju Salmon wrote:

Yes, the first one work fine. But i am wonder why we have removed -1 from expression. Since SQL expression clearly showing -1.

Because the CASE statement in SQL counts the 0 value for callattended = 0 and the -1 removes that. Not necessary for the set expression.

For help on the second, post a sample as I have already suggested.

Re: Set analysis question vs SQL query

Anonymous — Wed, 06 Jan 2016 05:41:11 GMT

Hi,

I don't think the count value will differ with the if condition.

Because Count(DISTINCT CASE WHEN callattended = 0 THEN 0 ELSE DATE END)-1 will count all the values so its enough if you use Count(Distinct Date)-1 itself.

This if condition will make sense if we use Sum() not Count().

In this you need to use simple if condition and not set analysis,

CASE WHEN SUM(callattended) = 0 THEN 0 ELSE (RC * CEILING(COUNT(DISTINCT Date))/22)

In Qlikview,

if(Sum(callattended)=0,0,(RC*floor(Count(Distinct Date))/22))

Regards,

Leni Balakrishnan

Re: Set analysis question vs SQL query

Anonymous — Wed, 06 Jan 2016 05:44:05 GMT

may be like this?

=(COUNT(DISTINCT {$<callattended= {'1'}>} DATE)) -1

=Floor(sum(if(callattended=0,0,((RC*Count(DISTINCT Date))/22))))