topic Re: Need to avoid duplicate value in load script in QlikView

Need to avoid duplicate value in load script

praveenkumar_s — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Need to avoid duplicate value in load script

sunny_talwar — Thu, 18 Jan 2018 11:07:13 GMT

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];

Re: Need to avoid duplicate value in load script

praveenkumar_s — Thu, 18 Jan 2018 11:21:10 GMT

Sunny,

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

Re: Need to avoid duplicate value in load script

sunny_talwar — Thu, 18 Jan 2018 11:22:00 GMT

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

Re: Need to avoid duplicate value in load script

sunny_talwar — Thu, 18 Jan 2018 11:23:56 GMT

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];

Re: Need to avoid duplicate value in load script

arroju_uday — Thu, 18 Jan 2018 11:28:14 GMT

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];

Re: Need to avoid duplicate value in load script

praveenkumar_s — Thu, 18 Jan 2018 13:11:05 GMT

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