topic Re: MEDIAN CALCULATION in QlikView

MEDIAN CALCULATION

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

Hi Dear all,

I need to calculate median (middle value) of the given data set under two Branches (MY00 & KW00). The aggr function given there wont get me the correct value. Kindly advise me what changes to be done in to the formula?

Thanks a lot.

Neville

Re: MEDIAN CALCULATION

Taoufiq_Zarra — Thu, 25 Jun 2020 21:46:34 GMT

@nevilledhamsiri

can you share the expected output (value) ?

Re: MEDIAN CALCULATION

nevilledhamsiri — Fri, 26 Jun 2020 02:21:58 GMT

Dear Taoufiq,

I have attached a sample. Please look in to this.

Thanks

Neville

Re: MEDIAN CALCULATION

Taoufiq_Zarra — Fri, 26 Jun 2020 10:31:20 GMT

Hi @nevilledhamsiri ,

One solution on script :

Data: LOAD * INLINE [ F1, F2 MY00, 1 MY00, 2 MY00, 11 MY00, 16 MY00, 25 MY00, 25 MY00, 28 MY00, 35 MY00, 71 MY00, 73 MY00, 77 MY00, 83 MY00, 93 MY00, 118 MY00, 144 MY00, 148 MY00, 165 MY00, 245 MY00, 288 MY00, 325 MY00, 338 MY00, 741 MY00, 815 MY00, 1 485 MY00, 2 275 MY00, 3 145 MY00, 3 656 MY00, 11 215 MY00, 35 325 MY00, 68 688 MY00, 129 959 MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, MY00, KW00, "1,00" KW00, "2,00" KW00, "7,00" KW00, "8,00" KW00, "16,00" KW00, "26,00" KW00, "27,00" KW00, "35,00" KW00, "37,00" KW00, "75,00" KW00, "81,00" KW00, "98,00" KW00, "112,00" KW00, "113,00" KW00, "121,00" KW00, "135,00" KW00, "185,00" KW00, "224,00" KW00, "225,00" KW00, "253,00" KW00, "265,00" KW00, "268,00" KW00, "334,00" KW00, "524,00" KW00, "561,00" KW00, "733,00" KW00, "765,00" KW00, "925,00" KW00, "935,00" KW00, "1 135,00" KW00, "1 247,00" KW00, "1 744,00" KW00, "2 719,00" KW00, "2 758,25" KW00, "3 925,00" KW00, "4 978,00" KW00, "5 788,00" KW00, "12 475,76" KW00, "13 227,00" KW00, "23 385,00" KW00, "28 833,00" KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, KW00, ]; left join load F1,median(F2) as median resident Data group by F1;

output :

and on UI, juste I use Median() !

Re: MEDIAN CALCULATION

nevilledhamsiri — Fri, 26 Jun 2020 16:36:04 GMT

Dear Taoufiq,

I'm bit confused as to how the data loading is done. Load inline feature was used as you suggested which causes some errors. I have attached the file with your script modification & please see the error. Also wish to have clarifications on these issues.

Should the Normal data loading of the file is necessary?

Is it only inline feature to be run as you noted?

How the zero values be renamed with just blank?

With our script modification, is there way of getting the desired out put with the help of aggr function as I'm not that good in script writing.

Thank you very much for your kind help & guidance

Neville

Note: I'm using a personnel edition which doesn't help me to view the qvd file

Re: MEDIAN CALCULATION

Taoufiq_Zarra — Fri, 26 Jun 2020 17:34:00 GMT

@nevilledhamsiri

the error in the script comes from omitting to add Data: at the beginning.

otherwise no need for inline

otherwise nulls can be ignored by where len(trim(Field))>0

Yes we can use aggr also

can you send the expected output from the new file

Re: MEDIAN CALCULATION

nevilledhamsiri — Fri, 26 Jun 2020 18:20:46 GMT

Dear Taoufiq,

If you don't mind could you please copy your entire script on a note pad (both source data & inline date) along with the formula applied to get the middle value as I am still getting an error. The file I loaded with original source data & the inline data below it is attached. Hope I'm not troubling you

Many Thanks.

Neville

Re: MEDIAN CALCULATION

Taoufiq_Zarra — Fri, 26 Jun 2020 18:26:41 GMT

There's no problem. Ask me your questions.

attached with no error, however I didn't understand your new need

Re: MEDIAN CALCULATION

nevilledhamsiri — Fri, 26 Jun 2020 18:31:56 GMT

Cannot open your qvd as I work on personnel edition, can you please copy your entire working in the body of the reply

Thnaks

Re: MEDIAN CALCULATION

Taoufiq_Zarra — Fri, 26 Jun 2020 18:34:36 GMT

I didn't change anything in your file

I just changed data: to Data:

Re: MEDIAN CALCULATION

nevilledhamsiri — Fri, 26 Jun 2020 19:07:07 GMT

Now the script error was gone & I can see the created new fields such as F1 & Median, but to populate the value for Median, what next to be done?

Re: MEDIAN CALCULATION

nevilledhamsiri — Fri, 26 Jun 2020 19:37:14 GMT

Dear Friend

Most of the issues I had seems to have resolved. To populate the value for median, expression to be made for Paid amount. Now the median for both Branches are visible as per the file attached.Many many thanks for helping again & again. Also if possible,let me know how the median is computed with out adding inline data. In other words, can we do this in the original data itself. Also please let me know the possible aggr function to get this results

Thanks

Re: MEDIAN CALCULATION

nevilledhamsiri — Sat, 27 Jun 2020 14:50:57 GMT

If possible please let me have the Aggr() function for the same calculation. I have almost got what needed from your guidance. Thanks a lot.

Neville

Re: MEDIAN CALCULATION

Taoufiq_Zarra — Sat, 27 Jun 2020 22:21:51 GMT

as you can see Neville its just aggr(Median(F2),F1)

Re: MEDIAN CALCULATION

nevilledhamsiri — Sun, 28 Jun 2020 13:43:40 GMT

Thank You very much

Nevile