csv.getColumn.if ( array ; matchColumn ; matchValue ; returnColumn ; delimiter )

Get column from array for match in another column

Average rating: 4.6 (32 votes) Log in to vote

Bruce Robertson - Show more from this author

Sample input:

csv.getColumn.if( "a;b;c¶a;x;23¶b;b;12"; 1; "a"; 3; ";")

Sample output:

c
23

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*
csv.getColumn.if( array; matchColumn; matchValue; returnColumn; delimiter)

Returns designated column from array where value in matchColumn = matchValue
Specify matchValue = "*" to return entire column

Bruce Robertson, Concise Design
Based on csv.getValue concept by Nicolas Bourré
v 1.01 1/26/2011
fixed "!" error

v 1.02 1/27/2011
Added option to handle matchValue as list

*/

Case( 
IsEmpty( array); "";
IsEmpty( matchValue); "";

ValueCount( matchValue) >1; 
Let([ 
mc = ValueCount( matchValue);
mv1 = GetValue( matchValue; 1);
mv2 = RightValues( matchValue ; mc-1 )];
List( 
csv.getcolumn.if( array; matchColumn; mv1; returnColumn; delimiter ) ;
csv.getcolumn.if( array; matchColumn; mv2; returnColumn; delimiter ) 
));

Let (
[
   vc= ValueCount( array); 
   r = GetValue (array; 1);
   delim = Case (not IsEmpty(delimiter); delimiter; ";");
   matchValue = GetValue( matchValue; 1);
   rowAsList = Substitute( r; delim; ¶);
   thisValue =  GetValue( rowAsList; matchColumn);
   isMatch = Case( matchValue = "*"; 1; 
   Left( matchValue; 1) = "!"; thisValue <> Right(matchValue; Length(matchValue)-1); 
    thisValue  = matchValue );
   thisMatch = Case( isMatch; GetValue( rowAsList; returnColumn));
   more = RightValues( array; vc-1)
];
  
List( 
thisMatch ; 
csv.getcolumn.if( more; matchColumn; matchValue; returnColumn; delim ) 
)
))

Get return delimited list of values in designated column of array if value in matchColumn = matchValue. Set matchValue to "*" to return entire column. If first char of matchValue = ! then return all non-matches.
v 1.01, fixed problem with "!" expression
v 1.02 1/27/2011
Added option to handle matchValue as list

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

csv.getColumn.if ( array ; matchColumn ; matchValue ; returnColumn ; delimiter )

Comments

Support this website.

Support this website.