Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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?
Thanks.
I will provide an example.
Regards.
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
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.