Set analysis question vs SQL query

[raju_salmon]

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.

[sujeetsingh]

Come with sample please

[jonathandienst · Accepted solution]

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.

[Anonymous]

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

[raju_salmon]

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.

[raju_salmon]

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.

[jonathandienst]

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.

[Anonymous]

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

[Anonymous]

may be like this?

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

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