Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Read about Qlik’s Response to COVID-19 Read the Letter, Join the Group.
Highlighted
Valued Contributor

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
Highlighted

Re: 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 ?

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
Highlighted
Valued Contributor

Re: 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 ?

stalwar1‌‌

Hi Sunny Bhai .. Please Help !!

Highlighted

Re: 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 ?

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

Highlighted
Valued Contributor

Re: 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 ?

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

Highlighted
Valued Contributor

Re: 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 ?

**update : data of 7000000 and increasing daily

Highlighted

Re: 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 ?

Where are you hard coding the locations?

Highlighted
Valued Contributor

Re: 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 ?

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

Highlighted

Re: 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 ?

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.

Highlighted
Valued Contributor

Re: 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 ?

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