Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
shekhar_analyti
Specialist
Specialist

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 ?

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 ?

1.png

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

1 Solution

Accepted Solutions
sunny_talwar

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))

View solution in original post

8 Replies
shekhar_analyti
Specialist
Specialist
Author

stalwar1‌‌

Hi Sunny Bhai .. Please Help !!

sunny_talwar

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

**update : data of 7000000 and increasing daily

sunny_talwar

Where are you hard coding the locations?

shekhar_analyti
Specialist
Specialist
Author

(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
Specialist
Specialist
Author

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