Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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;
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;