Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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')
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
];
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...
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
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
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