Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
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
Highlighted
MVP
MVP

Re: Distinct values of a field as parameter for Match function

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')

Highlighted
MVP
MVP

Re: Distinct values of a field as parameter for Match function

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

];

Highlighted
Not applicable

Re: Distinct values of a field as parameter for Match function

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...

Highlighted
Not applicable

Re: Distinct values of a field as parameter for Match function

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

Highlighted
Contributor III

Re: Re: Distinct values of a field as parameter for Match function

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

Highlighted
Not applicable

Re: Distinct values of a field as parameter for Match function

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