Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Replace count distinct by sum : bad results

Hello,

I have attached an example.

I want to test to replace count distinct big expression with many if conditions by sum with if conditions to compare calculation speed.

I used a table with my data (TMP) and an column containing Id

I have added a table with only the distinct Id and a column flag.

But with replacing my count distinct by a sum of the flag column, I have bad results.

Could you explain me what is the problem?

Cause to the filter conditions I can't use the peek style load script because the first line with the Id which obtain the flag may have other fields values which validate only certain condition and not others. But i want my count distinct evaluate expressions dynamically on selected data.

That why I have attempted to use a table (TMPDistinct)

to associate one single Id value to one single flag 1.

Regards,

13 Replies
swuehl
MVP
MVP

No, I believe you don't need to add each field in your set expression as dimension to the aggr() function. Not necessarily.

Not sure why your expression returns all zero. Are you still using the same 1 dimension CategoryTtl?

If you comment out some set modifiers in your set expression or the additional condition in the if() statement, does this change anything?

It's quite hard to debug this just looking at the expression (I don't see anything really wrong at the moment) and without knowing your data (model). Is there a chance that you post an updated sample file?

Not applicable
Author

Thanks.

I will provide an example.

Regards.

Not applicable
Author

Hello,

Here an example with a sample dataset CSV.

The issue is to sum on flag tables only, and not on AllRecord table, as long as I understand the bad results when not "0".

I don't know if it will really decrease the calculation time, but it's my goal to decrease it.

Regards

Not applicable
Author

Hi,

I have success to have the good results in my application using something like this

aggr(

     Count(

          {SET CLAUSE}

          DISTINCT

          IF(

               TrueDimension=FakeDimension1

               AND conditions dependant of variables

              , ID

          )

          ,FakeDimension1,FakeDimension2

)

Same thing for my Mt formula:

SUM(

     aggr(

          Min(

               {SET CLAUSE}

               IF(

                    TrueDimension=FakeDimension1

                    AND conditions dependant of variables

                   , Mt

                )

          )

          ,FakeDimension1,FakeDimension2,ID

     )

)

Edit: The statistics give me a good result, calculation time / 2 for  ten expression like this instead of use the "IF" ! Set analysis is really interesting for me here.