topic Fill Values for a Field in QlikView

Fill Values for a Field

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

Hello,

I have the following table:

GROUP	CODE	SALES	MEDIAN
12	1	29284,6	-
12	2	9230,8	-
12	3	18461,5	-
12	4	63907,7	8.769
12	5	7292,3	-
12	6	6553,8	-
12	7	7376,9	-
12	8	11076,9	-
12	9	6769,2	-
12	10	8307,7	-
16	26	9461,5	-
16	27	10984,6	9.342
16	28	8461,5	-
16	29	9223,1	-
17	30	17076,9	15.077
17	31	12923,1	-
17	32	15076,9	-

I calculate the MEDIAN field in my table as: AGGR(MEDIAN(SALES),GROUP). This is for every GROUP.

The thing is I'd need to fill that MEDIAN value for every GROUP and compare if SALES > MEDIAN then STATUS = 1, if not 0:

GROUP	CODE	SALES	MEDIAN	STATUS
12	1	29284,6	8.769	1
12	2	9230,8	8.769	1
12	3	18461,5	8.769	1
12	4	63907,7	8.769	1
12	5	7292,3	8.769	0
12	6	6553,8	8.769	0
12	7	7376,9	8.769	0
12	8	11076,9	8.769	1
12	9	6769,2	8.769	0
12	10	8307,7	8.769	0
16	26	9461,5	9.342	1
16	27	10984,6	9.342	1
16	28	8461,5	9.342	0
16	29	9223,1	9.342	0
17	30	17076,9	15.077	1
17	31	12923,1	15.077	0
17	32	15076,9	15.077	0

Finally count those CODES with STATUS = 1 and STATUS = 0:

CODE > MEDIAN (STATUS = 1)	8
CODE < MEDIAN (STATUS = 0)	9

Do you know how could I do that?

Thanks.

Re: Fill Values for a Field

ziadm — Thu, 27 Jul 2017 00:53:46 GMT

Hi micro

Try this in script .. you can achieve the same on the front end Table

// This is on the script

Table1:

load * Inline [

GROUP, CODE, SALES

12, 1, 29284

12, 2, 9230

12, 3, 18461

12, 4, 63907

12, 5, 7292

12, 6, 6553

12, 7, 7376

12, 8, 11076

12, 9, 6769

12, 10, 8307

16, 26, 9461

16, 27, 10984

16, 28, 8461

16, 29, 9223

17, 30, 17076

17, 31, 12923

17, 32, 15076 ];

Left Join

load GROUP,

Median(SALES) as MEDAIN

Resident Table1

Group by GROUP;

NoConcatenate

DataTable:

LOAD *,

if (SALES > MEDAIN,1,0) as STATUS

Resident Table1;

Re: Fill Values for a Field

microwin88x — Thu, 27 Jul 2017 01:22:13 GMT

Hello,

Isn't there any way to do it directly in my chart as an expression?

Because actually the median is dynamic and could be affected by multiple variables...

Re: Fill Values for a Field

ziadm — Thu, 27 Jul 2017 04:08:20 GMT

Yes ..

Create a straight table and your dimension is GROUP

expression

Median(SALES)

if(Median(SALES) > SALES,1,0)

Re: Fill Values for a Field

ziadm — Thu, 27 Jul 2017 04:10:03 GMT

you could the expression

Agg(Median(SALES),GROUP)

Re: Fill Values for a Field

devarasu07 — Thu, 27 Jul 2017 04:51:49 GMT

Hi Micro,

Seems same requirements, solutions already provided. help to close this duplicated thread. Thanks

=count({<CODE= {"=sum(SALES) >= Aggr(Median(Total <GROUP>SALES),GROUP,CODE)"} > } CODE)

Best Regards,

Deva