topic 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 ? in QlikView

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 — Wed, 25 Nov 2020 16:16:04 GMT

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

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 ?

shekhar_analyti — Mon, 28 May 2018 15:44:58 GMT

stalwar1‌‌

Hi Sunny Bhai .. Please Help !!

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 ?

sunny_talwar — Mon, 28 May 2018 16:35:31 GMT

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

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 ?

shekhar_analyti — Tue, 29 May 2018 08:58:14 GMT

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

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 ?

shekhar_analyti — Tue, 29 May 2018 09:03:30 GMT

**update : data of 7000000 and increasing daily

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 ?

sunny_talwar — Tue, 29 May 2018 10:58:10 GMT

Where are you hard coding the locations?

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 ?

shekhar_analyti — Tue, 29 May 2018 11:29:25 GMT

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

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

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 ?

sunny_talwar — Tue, 29 May 2018 11:35:19 GMT

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.

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 ?

shekhar_analyti — Tue, 29 May 2018 11:45:27 GMT

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