Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Distinct values of a field as parameter for Match function

How can I get the list of distinct values of a field so I can pass it as parameters to the match function.

Say I have the following INLINE data:

DIRECTOR:
LOAD * INLINE [
Director Name
Jason
Chris
];

STAFF:
LOAD * INLINE [
Staff Name
Regis
Mark
Chris
David
Jason
Wee
];

I am trying to create a table that shows me all staff and as expression "director" or "non-director" depending on whether it exists in the DIRECTOR table.

I have tried =If(Match([Staff Name],[Director Name]),'Director','Non-Director')

It works as long as I select something in the Director field. That's not what I want. I want the returned value to be independent from what is selected or not in the DIRECTOR list.

6 Replies
swuehl
MVP
MVP

You would need an aggregation here.

Assuming a chart with dimension [Staff Name], maybe like

= if( sum( match([Director Name], [Staff Name]) ), 'Director', 'Non-Director')

maxgro
MVP
MVP

DIRECTOR:

LOAD * INLINE [

Director Name

Jason

Chris

];

a:

load

  chr(39) & Concat([Director Name], chr(39) & ',' & chr(39)) & chr(39) as concatfield

resident DIRECTOR;

LET var = peek('concatfield');

trace $(var);

STAFF:

load *, if(match([Staff Name], $(var)),1,0) as flag;

LOAD * INLINE [

Staff Name

Regis

Mark

Chris

David

Jason

Wee

];

Not applicable
Author

Thanks Massimo. I feel it is an over-complicated solution for something that can be done easily in Excel though.

Sometimes I feel complicated things can be done easily in Qlikview but simple things require extensive scripting...

Not applicable
Author

Hello,

if you would like to do it in script, try this:

People:

LOAD *,'Director' as level INLINE [

Name

Jason

Chris

];

LOAD *, 'No Director' as level INLINE [

Name

Regis

Mark

Chris

David

Jason

Wee

] Where not Exists(Name);

regards

Darek

daveamz
Partner - Creator III
Partner - Creator III

Hi,

Another way to achive this, in front-end only, is to use a variable like

vDirector = =concat({1}chr(39)&[Director Name]&chr(39),',')

and the use it in expression : If(Match([Staff Name],$(vDirector)),'Director','Non-Director')

I think the best way is to create a flag using ApplyMap:

DIRECTOR:

Mapping

LOAD [Director Name] as Staff, 'Director' as Position INLINE [

Director Name

Jason

Chris

];

STAFF:

LOAD *, ApplyMap('DIRECTOR',[Staff Name], 'Non-Director') as _flagIsDirector INLINE [

Staff Name

Regis

Mark

Chris

David

Jason

Wee

];

Regards,

David

Not applicable
Author

Thanks Dariusz and David,

I couldn't sleep over this so I tried a bunch of stuff. I found a pretty easy way inspired from SQL and thought I'd share it.

DIRECTOR:

LOAD * INLINE [

Director Name

Jason

Chris

];

STAFF:

LOAD *, if(exists([Director Name],[Staff Name]),'Director','Non-Director') As Status INLINE [

Staff Name

Regis

Mark

Chris

David

Jason

Wee

];

Works like wonder!

Wee