Get relevant values under the same key

[kfir1987]

Hi, 

I am facing a problem during my project. I have 2 columns:

Key (string),

SC (string)

for each key I can find few SC. I have 3 value under SC column: direct, indirect and N/A.

my question: 

In case key contain 2 instances of SC, that one of them is N/A and the second one is direct or indirect then show only the indirect or direct instance.

for example: 

Key      SC

1             N/A

1             Indirect 

2             Direct 

3             N/A

3             Direct

So in the end I want to see the below table:

Key      SC

1             Indirect 

2             Direct 

3             Direct

I want to do it in the editor.

Thanks for the help 

[agni_gold]

T:
LOAD * INLINE [
Key, SC
1, N/A
1, Indirect
2, Direct
3, N/A
3, Direct
];
Join Load Key , count(SC) as SC_Count Resident T Group by Key;

Final_Set:
Load Key , SC Resident T where SC_Count=1;Concatenate
LOAD Key , SC Resident T where SC_Count<>1 and SC<>'N/A';

DROP Table T;

[kfir1987]

Hi,
This is not good enough, this solution useful for 2,3,4 keys. I am looking for a solution for thousands of keys.
Thanks

[agni_gold]

I would request can you share some data over which you are working, so that we can provide you a perfect solution.

[sunny_talwar]

This may be another option

Table:
LOAD Key,
	 SC,
	 Match(SC, 'Direct', 'Indirect', 'N/A') as SCNum;
LOAD * INLINE [
    Key, SC
    1, N/A
    1, Indirect
    2, Direct
    3, N/A
    3, Direct
];

FinalTable:
NoConcatenate
LOAD *
Resident Table
Order By Key, SCNum;

STORE FinalTable into Temp.qvd (qvd);
DROP Table Table, FinalTable;

Table:
LOAD Key,
	 SC
FROM Temp.qvd (qvd)
Where not Exists (Key);

[sasis551]

Hi 

try this script .may be it would helpful to you

load 

Key,

SC

from location where match(SC,'Direct','Indirect');