Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
nicolai_moller
New Contributor

remove unwanted values

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.

6 Replies
MVP & Luminary
MVP & Luminary

Re: remove unwanted values

Maybe something like this:

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

- Marcus

Partner
Partner

Re: remove unwanted values

or directly :

Load Key_ID, ID,

From xyz

where

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

;

buzzy996
Honored Contributor II

Re: remove unwanted values

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
Contributor II

Re: remove unwanted values

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
New Contributor

Re: remove unwanted values

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

tcullinane
Contributor II

Re: remove unwanted values

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.