how to load if same id has two records with status Y and N ,if unique id has status with Y

[MEDHA07]

Hi All,

I want to load the data only where One number has two rows with status Y and N and also unique number has one row with status Y.

Num	unit	grp	Status	Currency	Rate
123	Com	EQ	Y	usd	1
123	com	EQ	N	null	null
456	com	EQ	Y	cad	0.3
678	com	EQ	Y	usd	1
987	com	EQ	N	null	null
788	com	EQ	N	usd	1

Results expecting below

Num	unit	grp	Status	Currency	Rate
123	Com	EQ	Y	usd	1
123	com	EQ	N	null	null
456	com	EQ	Y	cad	0.3
678	com	EQ	y	usd	1

[mightyqlikers]

Temp:
LOAD * INLINE [
Num, Unit, GRP, Status, Currency, Rate
123, Com, EQ, Y, usd, 1
123, com, EQ, N, null, null
456, com, EQ, Y, cad, 0.3
678, com, EQ, Y, usd, 1
987, com, EQ, N, null, null
788, com, EQ, N, usd, 1
];

Temp_1:
LOAD
Num,
count(Num) as NumCnt,
Concat(Status) as StatusCnt
Resident Temp group by Num;

Inner Join(Temp)
LOAD
Num
Resident Temp_1 where NumCnt>=1 and match(StatusCnt,'YN','NY','Y');

drop Table Temp_1;

[Vegar · Accepted solution]

You could do this.

Tmp:

LOAD Num FROM Source

WHERE Status 'Y';

Data:

LOAD Num, unit, grp, Status, Currency, Rate 

FROM Source

WHERE exists(Num);

Drop table Tmp;