help me to ignore the records based on condition?

[durgabhavani]

Hi All,

Please help me to ignore the records if the emp having the Emp Code SE1. please find my data and expected out put and advice.

My data:

Emp ID	Emp Name	Emp code
101	Ravi	SE1
101	Ravi	TL1
102	Suresh	SE1
103	Satish	SSE1
104	Ramesh	SE1
104	Ramesh	PM
105	Venkat	PM

Expected out put.

Emp ID	Emp Name	Emp code
103	Satish	SSE1
105	Venkat	PM

please help me to achieve the above output.

Thanks,

Durga

[ecolomer]

Is this?

In the script

FILE_TEST:

LOAD * Inline [

Emp ID, Emp Name, Emp code

101, Ravi, SE1

101, Ravi, TL,1

102, Suresh, SE1

103, Satish, SSE1

104, Ramesh, SE1

104, Ramesh, PM

105, Venkat, PM

];

QUALIFY *;

New:

LOAD * resident FILE_TEST

where [Emp code] <> 'SE1';

[swuehl]

Maybe like

FILE_TEST:

LOAD * Inline [

Emp ID, Emp Name, Emp code

101, Ravi, SE1

101, Ravi, TL,1

102, Suresh, SE1

103, Satish, SSE1

104, Ramesh, SE1

104, Ramesh, PM

105, Venkat, PM

];

Right JOIN

LOAD [Emp ID]

WHERE CodeCheck =0;

LOAD [Emp ID],

     Sum(If( [Emp code] ='SE1',1,0)) as CodeCheck

Resident FILE_TEST

GROUP BY [Emp ID];

Emp code	Emp ID	Emp Name
PM	105	Venkat
SSE1	103	Satish

[tamilarasu]

Hi Durga,

Data:

LOAD * INLINE [

    Emp ID, Emp Name, Emp code

    101, Ravi, SE1

    101, Ravi, TL1

    102, Suresh, SE1

    103, Satish, SSE1

    104, Ramesh, SE1

    104, Ramesh, PM

    105, Venkat, PM

];

Right Join

Load * Where Not WildMatch(Flag,'SE1*');

Load [Emp ID],Concat([Emp code],'|',-Match([Emp code],'SE1')) as Flag

Resident Data Group by [Emp ID];

Drop Field Flag;

[Peter_Cammaert]

We already have JOIN & KEEP prefixes in the Load Script language. It would be nice if we could have an EXCLUDE (or ELIMINATE, or FILTER, or any better name) prefix too...

[swuehl]

Interesting idea, Peter, how should this look like, in your opinion?

[Peter_Cammaert]

How about replacing all code after the INLINE LOAD in your and Tamil's example with something like (replace InlineTable with Data or FILE_TEST😞

RIGHT EXCLUDE(InlineTable)

LOAD [Emp ID]

RESIDENT [InlineTable]

WHERE [Emp Code] = 'SE1'; // Throw out what we don't like in a pseudo-single step

Would be a compact form of something that doesn't exist in SQL (still the progenitor of QV script). I'm not going to play devil's advocate right now although I'm eager to shoot holes in it myself. Mostly because I can't really imagine how to extend and implement this in code that is both high performance, consistent and flexible on top...

hmmm..

[durgabhavani]

Hi Stefin,

How to change your code if i want to ignore other code PM as well?

Thanks,

Durga

[swuehl]

Change this line

   Sum(If( [Emp code] ='SE1',1,0)) as CodeCheck

to

   Sum(If( Match([Emp code],'SE1','OtherCode') ,1,0)) as CodeCheck

[Kushal_Chawda]

another way

Data:

LOAD * Inline [

Emp ID, Emp Name, Emp code

101, Ravi, SE1

101, Ravi, TL,1

102, Suresh, SE1

103, Satish, SSE1

104, Ramesh, SE1

104, Ramesh, PM

105, Venkat, PM

];

Left Join(Data)

LOAD Distinct [Emp ID] ,

     1 as CheckEmpID

Resident Data

where [Emp code]='SE1';

Final:

NoConcatenate

LOAD *

Resident Data

where IsNull(CheckEmpID);

DROP Tables Data;