Group by function

[#1]

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.

[sunny_talwar]

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;

[MindaugasBacius]

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?