Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
I would request can you share some data over which you are working, so that we can provide you a perfect solution.
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);
Hi
try this script .may be it would helpful to you
load
Key,
SC
from location where match(SC,'Direct','Indirect');