Need to avoid duplicate value in load script

[praveenkumar_s]

Hi friends,

I'm loading employee no and their status in load script. But some employee number having two status(Active and Removed). In that case i want to load only 'Removed ' status for that Employee in load script.

Kindly find the Below screenshot for reference. Any help will be much appreciated.

[sunny_talwar]

Try like this

Table:

LOAD *,

Match(Status, 'Active', 'Removed') as StatusNum;

LOAD * INLINE [

    Empl No, Status

    1, Active

    2, Removed

    3, Active

    3, Removed

    4, Removed

    5, Active

];

Right Join (Table)

LOAD [Empl No],

Max(StatusNum) as StatusNum

Resident Table

Group By [Empl No];

[praveenkumar_s]

Sunny,

Im getting number 2 instead of Removed. Kindly tell me what am i done wrong?

[sunny_talwar]

For StatusNum... you might be getting 2, but status should be Removed

[sunny_talwar · Accepted solution]

Alternatively, you can try this

Table:

LOAD * INLINE [

    Empl No, Status

    1, Active

    2, Removed

    3, Active

    3, Removed

    4, Removed

    5, Active

];

Right Join (Table)

LOAD [Empl No],

MaxString(Status) as Status

Resident Table

Group By [Empl No];

[arroju_uday]

Hi praveen

Try this. You will get one Record if it is Removed.

load

[Empl no],

maxstring(Status) as  Status

From

Group By [Empl no];

[praveenkumar_s]

Exactly what i want. Thank you so much for your Knowledge.