Best record( Best Column Value) Deducing to single record

[fallenangel6]

Hi All,

I have data similar to the below table

clsid	Source	partyrk	accountrk	pan	Pan Indicator	bankacc	bank acc indicator	mobile	mobile indicator
1	A	2	11	ABCXXA	Valid	1223311	Invalid	123334544	Valid
1	A	2	12	AVDQW	Invalid	1556345	Valid	45568322	Invalid
1	A	4	13	QEEWRT	Invalid		NA	25567788	Invalid
1	A	4	14	TEQTRR	NA	3776443	Valid	2112333	Valid
1	A	4	15	UEWIOP	Valid	4.53E+07	Invalid		NA
1	B	3	16	AAERDSS	Valid	qq3e	Invalid	1666544	Invalid
1	B	5	17		NA	943321464	Valid	1566542	Invalid
1	B	6	18	QXCTDR	Invalid	q23w	Invalid	87642527	Valid

My desired output is

clsid	Source	partyrk	accountrk	pan	Pan Indicator	bankacc	bank acc indicator	mobile	mobile indicator
1	A	4	15	ABCXXA	Valid	1556345	Valid	123334544	Valid
1	B	6	18	AAERDSS	Valid	943321464	Valid	87642527	Valid

for each combination of clsid and source, i need best indicator and best data.

For partyrk and accountrk, i take max(partyrk) and max(accountrk). 

Please help.

[lanlizgu]

Supposing that your first table is named Table1, let's try the following:

OutputTable:
LOAD
clsid,
Source,
max(partyrk) as partyrk,
max(accountrk) as accountrk
resident Table1
group by clsid, Source;

left join (OutputTable)
load
clsid,
Source,
partyrk,
accountrk,

pan,
[Pan Indicator],
bankacc,
[bank acc indicator],
mobile,
[mobile indicator]
resident Table1

drop Table1;

If you provide some sample data in a qvd I can give a try and just do it in one sentence without the left join.

Thank you.

[lanlizgu]

Based on your data you can use the following. My question is, what criteria are you using to obtain pan, pan indicator, bankacc, bank acc indicator, mobile and mobile indicator?

Table1:
LOAD * INLINE [
clsid, Source, partyrk, accountrk, pan, 'Pan Indicator', bankacc, 'bank acc indicator', mobile, 'mobile indicator'
1, A, 2, 11, ABCXXA, Valid, 1223311, Invalid, 123334544, Valid
1, A, 2, 12, AVDQW, Invalid, 1556345, Valid, 45568322, Invalid
1, A, 4, 13, QEEWRT, Invalid, , NA, 25567788, Invalid
1, A, 4, 14, TEQTRR, NA, 3776443, Valid, 2112333, Valid
1, A, 4, 15, UEWIOP, Valid, 4.53E+07, Invalid, , NA
1, B, 3, 16, AAERDSS, Valid, qq3e, Invalid, 1666544, Invalid
1, B, 5, 17, , NA, 943321464, Valid, 1566542, Invalid,
1, B, 6, 18, QXCTDR, Invalid, q23w, Invalid, 87642527, Valid
];

OutputTable:
LOAD
clsid,
Source,
max(partyrk) as partyrk,
max(accountrk) as accountrk
resident Table1
group by clsid, Source;

left join (OutputTable)
load
clsid,
Source,
partyrk,
accountrk,

pan,
[Pan Indicator],
bankacc,
[bank acc indicator],
mobile,
[mobile indicator]
resident Table1;

drop table Table1;