Join with Distinct Column - Backend Script

[Purushothaman]

Hi Experts,

Below is my Table:

My Requirement is:

I need to get the distinct PartyID as a additional column in the same table where AccountStatus = A . 

Expected Output:

I have the attached the same. Anyone Please help!!

Thank you,

[Digvijay_Singh · Accepted solution]

Pl check if something like this is needed - 

input:
LOAD
ACCID,
PARTYID,
AccountStatus,
ACOpendate
FROM [lib://Download/1901_Req.xlsx]
(ooxml, embedded labels, table is Raw)
;

NoConcatenate
final:
Load *,
If(Previous(PARTYID)=Null() OR PARTYID <> Previous(PARTYID),If(AccountStatus='A',PARTYID,null())) as PARTYID2
resident input
order by PARTYID
;

drop table input;

[Purushothaman · Accepted solution]

DATA:
LOAD
ACCID,
PARTYID,
AccountStatus,
ACOpenDate
FROM
[C:\Users\VIVEK\Documents\Guru\Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

NOCONCATENATE

Main:
LOAD
ACCID,
PARTYID,
AccountStatus,
ACOpenDate,
IF(MATCH(AccountStatus,'A','B'),
IF(MATCH(PARTYID,PEEK(PARTYID)),NULL(),PARTYID)) AS DISTINCT_PARTYID
RESIDENT DATA
ORDER BY PARTYID;

DROP TABLE DATA;