remove unwanted values

[nicolai_moller]

Hi

I have a table like this, where ID's are grouped so one of the IDs is the key_id for the group:

Key_ID	ID
a123b	a123b
g123h	g990p
g123h	g123h
g435l	g435l
g435l	p897p
q123t	q123t

I'm trying to create a flag or something so I can get a table that shows all situations where Key_ID contains '123' in the value and ID

also has '123' in the value. But if an ID does not have '123' in the value, the Key_ID and all ID's sssociated with it should be removed.

So in the end I want a table like this:

Key_ID	ID
a123b	a123b
q123t	q123t

Thanks.

[marcus_sommer]

Maybe something like this:

Load Key_ID, ID, if(wildmatch(Key_ID, '*123*') and wildmatch(ID, '*123*'), 1, 0) as Flag From xyz;

- Marcus

[giakoum]

or directly :

Load Key_ID, ID,

From xyz

where

wildmatch(Key_ID, '*123*') and wildmatch(ID, '*123*')

;

[buzzy996]

try this,

tab2:

LOad * inline [

Key_ID,ID

a123b,a123b

g123h,g990p

g123h,g123h

g435l,g435l

g435l,p897p

q123t,q123t

];

Tab3:

NoConcatenate Load

Key_ID,

ID

Resident tab2

where WildMatch(Key_ID,'*123*') and WildMatch(ID,'*123*');

Drop table tab2;

[saurabh5]

Test:

LOAD

Key_ID,
ID,
if(wildmatch(Key_ID, '*123*') and wildmatch(ID, '*123*'), 1, 0) as

[New Field]

FROM

(ooxml, embedded labels, table is [test]);

;

[nicolai_moller]

Your solutions includes Key_ID g123h, but that field is also assocated with ID g990p, so that Key_ID should not be displayed.

[tcullinane]

try something like creating a new field that concatenates the ID's grouped by the Key_ID, then use the wildmatch logic to give the list of correct keyfields. Possibly then use a resident load to load from the original table if the ID fields are still required in final table.