Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I didn't change anything in your file
I just changed data: to Data:
as you can see Neville its just aggr(Median(F2),F1)
can you share the expected output (value) ?
Dear Taoufiq,
I have attached a sample. Please look in to this.
Thanks
Neville
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() !
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
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
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
There's no problem. Ask me your questions.
attached with no error, however I didn't understand your new need
Cannot open your qvd as I work on personnel edition, can you please copy your entire working in the body of the reply
Thnaks
I didn't change anything in your file
I just changed data: to Data: