How to fix problem with of a set expression 1 , so that i get desired result similar to set expression 2 with least hard coding of values ?

[shekhar_analyti]

Hi All ,

How to fix problem with of a set expression 1 , so that i get desired result similar to set expression 2 with least hard coding of values ?

THOUGH DATA IN FIRST THREE COLUMNS OF BOTH TABLE ARE COMING SAME WITH DIFFERENT SET EXPRESSION ,

WHAT SHOULD BE DONE , TO MAKE THE LAST TWO COLUMN OF 1ST TABLE SHOW SAME DATA LIKE LAST TWO COLUMN OF TABLE 2 .

Thanks & Regards

Shekar

[shekhar_analyti]

stalwar1‌‌

Hi Sunny Bhai .. Please Help !!

[sunny_talwar · Accepted solution]

Try this as your variable's expression for vTotal

=Sum({<ACTION_LOCATION = {"*"}>}Aggr((Count(DISTINCT {<ACTION_TYPE={'A'},F5MIN_FLAG={1}>}ACTION_ID)*10+

Count(DISTINCT {<ACTION_TYPE={'A'},F10MIN_FLAG={1}>}ACTION_ID)*20+

Count(DISTINCT {<ACTION_TYPE={'A'},F15MIN_FLAG={1}>}ACTION_ID)*50+

Count(DISTINCT {<ACTION_TYPE={'A'},F20MIN_FLAG={1}>}ACTION_ID)*120+

Count(DISTINCT {<ACTION_TYPE={'A'},F30MIN_FLAG={1}>}ACTION_ID)*150)

+

(Count(DISTINCT {<ACTION_TYPE={'B'},F5MIN_FLAG={1}>}ACTION_ID)*10+

Count(DISTINCT {<ACTION_TYPE={'B'},F10MIN_FLAG={1}>}ACTION_ID)*20+

Count(DISTINCT {<ACTION_TYPE={'B'},F15MIN_FLAG={1}>}ACTION_ID)*50+

Count(DISTINCT {<ACTION_TYPE={'B'},F20MIN_FLAG={1}>}ACTION_ID)*120+

Count(DISTINCT {<ACTION_TYPE={'B'},F30MIN_FLAG={1}>}ACTION_ID)*150), ACTION_LOCATION))

[shekhar_analyti]

Thank You Sunny Bhai ...

Just wanted to check , how about applying above logic to data of 7000000 .

Which will be better the expression you gave or literally hard coding all possible Location like i did earlier .

Thanks & Regards

Shekar

[shekhar_analyti]

**update : data of 7000000 and increasing daily

[sunny_talwar]

Where are you hard coding the locations?

[shekhar_analyti]

(Count(DISTINCT {<ACTION_TYPE={'A'},F5MIN_FLAG={1},ACTION_LOCATION={'USA'}>}ACTION_ID)*10+

Count(DISTINCT {<ACTION_TYPE={'A'},F10MIN_FLAG={1},ACTION_LOCATION={'USA'}>}ACTION_ID)*20+

Count(DISTINCT {<ACTION_TYPE={'A'},F15MIN_FLAG={1},ACTION_LOCATION={'USA'}>}ACTION_ID)*50+

Count(DISTINCT {<ACTION_TYPE={'A'},F20MIN_FLAG={1},ACTION_LOCATION={'USA'}>}ACTION_ID)*120+

Count(DISTINCT {<ACTION_TYPE={'A'},F30MIN_FLAG={1},ACTION_LOCATION={'USA'}>}ACTION_ID)*150)

+

(Count(DISTINCT {<ACTION_TYPE={'A'},F5MIN_FLAG={1},ACTION_LOCATION={'Prussia'}>}ACTION_ID)*10+

Count(DISTINCT {<ACTION_TYPE={'A'},F10MIN_FLAG={1},ACTION_LOCATION={'Prussia'}>}ACTION_ID)*20+

Count(DISTINCT {<ACTION_TYPE={'A'},F15MIN_FLAG={1},ACTION_LOCATION={'Prussia'}>}ACTION_ID)*50+

Count(DISTINCT {<ACTION_TYPE={'A'},F20MIN_FLAG={1},ACTION_LOCATION={'Prussia'}>}ACTION_ID)*120+

Count(DISTINCT {<ACTION_TYPE={'A'},F30MIN_FLAG={1},ACTION_LOCATION={'Prussia'}>}ACTION_ID)*150)

+

(Count(DISTINCT {<ACTION_TYPE={'A'},F5MIN_FLAG={1},ACTION_LOCATION={'France'}>}ACTION_ID)*10+

Count(DISTINCT {<ACTION_TYPE={'A'},F10MIN_FLAG={1},ACTION_LOCATION={'France'}>}ACTION_ID)*20+

Count(DISTINCT {<ACTION_TYPE={'A'},F15MIN_FLAG={1},ACTION_LOCATION={'France'}>}ACTION_ID)*50+

Count(DISTINCT {<ACTION_TYPE={'A'},F20MIN_FLAG={1},ACTION_LOCATION={'France'}>}ACTION_ID)*120+

Count(DISTINCT {<ACTION_TYPE={'A'},F30MIN_FLAG={1},ACTION_LOCATION={'France'}>}ACTION_ID)*150)

+

(Count(DISTINCT {<ACTION_TYPE={'A'},F5MIN_FLAG={1},ACTION_LOCATION={'UK'}>}ACTION_ID)*10+

Count(DISTINCT {<ACTION_TYPE={'A'},F10MIN_FLAG={1},ACTION_LOCATION={'UK'}>}ACTION_ID)*20+

Count(DISTINCT {<ACTION_TYPE={'A'},F15MIN_FLAG={1},ACTION_LOCATION={'UK'}>}ACTION_ID)*50+

Count(DISTINCT {<ACTION_TYPE={'A'},F20MIN_FLAG={1},ACTION_LOCATION={'UK'}>}ACTION_ID)*120+

Count(DISTINCT {<ACTION_TYPE={'A'},F30MIN_FLAG={1},ACTION_LOCATION={'UK'}>}ACTION_ID)*150)

vs

Sum({<ACTION_LOCATION = {"*"}>}Aggr((Count(DISTINCT {<ACTION_TYPE={'A'},F5MIN_FLAG={1}>}ACTION_ID)*10+

Count(DISTINCT {<ACTION_TYPE={'A'},F10MIN_FLAG={1}>}ACTION_ID)*20+

Count(DISTINCT {<ACTION_TYPE={'A'},F15MIN_FLAG={1}>}ACTION_ID)*50+

Count(DISTINCT {<ACTION_TYPE={'A'},F20MIN_FLAG={1}>}ACTION_ID)*120+

Count(DISTINCT {<ACTION_TYPE={'A'},F30MIN_FLAG={1}>}ACTION_ID)*150)

+

(Count(DISTINCT {<ACTION_TYPE={'B'},F5MIN_FLAG={1}>}ACTION_ID)*10+

Count(DISTINCT {<ACTION_TYPE={'B'},F10MIN_FLAG={1}>}ACTION_ID)*20+

Count(DISTINCT {<ACTION_TYPE={'B'},F15MIN_FLAG={1}>}ACTION_ID)*50+

Count(DISTINCT {<ACTION_TYPE={'B'},F20MIN_FLAG={1}>}ACTION_ID)*120+

Count(DISTINCT {<ACTION_TYPE={'B'},F30MIN_FLAG={1}>}ACTION_ID)*150), ACTION_LOCATION))

[sunny_talwar]

Oh, are you asking which one is better between the two expressions for 7000000 + growing row of data?

I think set analysis is usually better, but if there are only 4 ACTION_LOCATION, it might not be too difficult to maintain the expression, but as it gets to grow, the maintenance will be an issue.

With regards to the Aggr() function, I don't think it should be too bad because we are only Aggregating for one dimension. but this should be something you will have to test and verify.

[shekhar_analyti]

Actually I will have max of 5 locations of which 4 i have already shown .

" I think set analysis is usually better " .. If that is your point then i will go ahead with it . Yhank you