topic Re: Group by function in QlikView

Group by function

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have an issue,

PROC_ID	PERIL_CD	TRTY_SHRT_TXT	NAME	YEAR	LOSS	EVNT_ID	REC
1872	EQ	CNTY	AAA	1	2910	1111	1000
1872	EQ	CNTY	AAA	1	2700	222	2000
1872	EQ	NP	AAA	2	2780	333	3000
1872	EQ	NP	AAA	2	2890	444	1500
1872	EQ	MM	AAA	3	7887	555	600
1872	EQ	CNTY	AAA	4	6767	666	0
1872	EQ	CNTY	AAA	4	6767	777	0

from this table i need MAX(REC) by PERIL_CD,TRTY_SHRT_TXT and YEAR.

following is expected output.

PROC_ID	PERIL_CD	TRTY_SHRT_TXT	NAME	YEAR	LOSS	EVNT_ID	REC
1872	EQ	CNTY	AAA	1	2700	222	2000
1872	EQ	NP	AAA	2	2780	333	3000
1872	EQ	MM	AAA	3	7887	555	600
1872	EQ	CNTY	AAA	4	6767	777	0

when rec is same consider any one of the record.

Re: Group by function

sunny_talwar — Thu, 26 May 2016 18:45:03 GMT

May be try this:

Table:

LOAD PROC_ID,

PERIL_CD,

TRTY_SHRT_TXT,

NAME,

YEAR,

LOSS,

EVNT_ID,

REC,

REC * 1000000 + EVNT_ID as NewColumn

FROM

[https://community.qlik.com/thread/218507]

(html, codepage is 1252, embedded labels, table is @1);

Right Join (Table)

LOAD PROC_ID,

PERIL_CD,

TRTY_SHRT_TXT,

YEAR,

Max(NewColumn) as NewColumn

Resident Table

Group By PROC_ID, PERIL_CD, TRTY_SHRT_TXT, YEAR;

Re: Group by function

MindaugasBacius — Thu, 26 May 2016 18:53:28 GMT

Try this:

Data:
LOAD PERIL_CD,
  TRTY_SHRT_TXT, 
     YEAR,  
     max(REC) as REC
FROM

(ooxml, embedded labels, table is Sheet1)
Group by PERIL_CD, TRTY_SHRT_TXT, YEAR;


Left join (Data)
LOAD PROC_ID, 
     PERIL_CD, 
     TRTY_SHRT_TXT, 
     NAME, 
     YEAR, 
     LOSS, 
     EVNT_ID,
  REC
FROM

(ooxml, embedded labels, table is Sheet1);

The outcome:

Just one issue appears. How did you come up with the logic for year 4?