topic Re: remove unwanted values in QlikView

remove unwanted values

nicolai_moller — Fri, 20 Mar 2015 15:42:30 GMT

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.

Re: remove unwanted values

marcus_sommer — Fri, 20 Mar 2015 15:46:27 GMT

Maybe something like this:

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

- Marcus

Re: remove unwanted values

giakoum — Fri, 20 Mar 2015 15:48:52 GMT

or directly :

Load Key_ID, ID,

From xyz

where

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

;

Re: remove unwanted values

buzzy996 — Fri, 20 Mar 2015 15:58:59 GMT

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,

Resident tab2

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

Drop table tab2;

Re: remove unwanted values

saurabh5 — Fri, 20 Mar 2015 16:06:56 GMT

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]);

;

Re: remove unwanted values

nicolai_moller — Fri, 20 Mar 2015 17:20:40 GMT

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

Re: remove unwanted values

tcullinane — Fri, 20 Mar 2015 17:29:50 GMT

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.