MEDIAN CALCULATION

[nevilledhamsiri]

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

[Taoufiq_Zarra]

@nevilledhamsiri 

can you share the expected output (value) ?

[nevilledhamsiri]

Dear Taoufiq,

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

Thanks

Neville

[Taoufiq_Zarra]

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

[nevilledhamsiri]

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

[Taoufiq_Zarra]

@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

[nevilledhamsiri]

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

[Taoufiq_Zarra]

There's no problem. Ask me your questions.

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

[nevilledhamsiri]

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

Thnaks

[Taoufiq_Zarra · Accepted solution]

I didn't change anything in your file

I just changed data: to Data: