How to avaid

[subbareddykm]

Hi all,

i have one scenario,where i need to avoid some ID's

Condition is : If ID of status=N, i dont want to load that ID.

Ex:

In  my data

ID  ARP  Status

1	E	Y
1	N	N
1	S
1	W	NY

I dont want this ID of any data.

Can any one tell me how to do this ?

[subbareddykm]

As per my condition:

Expecting like this :

ID	ARP	Status
3	E	Y
4	W	NY
6	E
12	N	Y

[MK_QSL · Accepted solution]

Data:

LOAD * INLINE [

    ID, ARP, Status

    1, E, Y

    1, N, N

    1, W, NY

    1, S

    2, N, N

    3, E, Y

    4, W, NY

    5, S, N

    5, N, Y

    6, E,

    8, W, N

    8, S, Y

    12, N, Y

    42, E, N

    42, W, Y

    42, S, NP

];

Left Join

Load ID, 1 as Flag Resident Data where Status = 'N';

NoConcatenate

Final:

Load ID, ARP, Status Resident Data Where Flag <> 1;

Drop Table Data;

[subbareddykm]

Great Manish,

Like that i have one scenario :

Is it posible  ?

flag

[MK_QSL]

Unable to understand your question of flag !

[sunny_talwar]

May be this:

Table:

LOAD * INLINE [

    ID, ARP, Status

    1, E, Y

    1, N, N

    1, W, NY

    1, S

    2, N, N

    3, E, Y

    4, W, NY

    5, S, N

    5, N, Y

    6, E,

    8, W, N

    8, S, Y

    12, N, Y

    42, E, N

    42, W, Y

    42, S, NP

];

Right Join (Table)

LOAD ID

Where SubStringCount(Concat, '|N|') = 0;

LOAD ID,

  '|' & Concat(Status, '|') & '|' as Concat

Resident Table

Group By ID;

[subbareddykm]

The conditionis IF D.No has Status=H,S only that records i dont want to load .

Ex :

D.No Rank Staus

1        105   S

1         106   H

1         106  S

I want like below :

D.No Rank Staus

1         106  S

[MK_QSL]

Give one more example... This is not enough. You said don't want to Load D.No having H and S Status. Then why you have below output?

D.No Rank Staus

1         106  S