topic Distinct values of a field as parameter for Match function in QlikView

Distinct values of a field as parameter for Match function

Thu, 15 May 2014 18:34:58 GMT

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.

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

swuehl — Thu, 15 May 2014 18:56:52 GMT

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

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

maxgro — Thu, 15 May 2014 19:00:19 GMT

DIRECTOR:

LOAD * INLINE [

Director Name

Jason

Chris

];

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

];

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

Thu, 15 May 2014 22:45:30 GMT

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

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

Fri, 16 May 2014 10:53:03 GMT

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

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

daveamz — Fri, 16 May 2014 12:18:50 GMT

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

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

Sat, 17 May 2014 11:46:52 GMT

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