PivotArray ( ArrayText )

Transforms a Return and Comma separated (.csv) list of data to "pivoted" form.

Be the first to rate this function Log in to vote

Mark D Kerr - Show more from this author
Discovery Reef Design & Consulting

Sample input:

2018,847000,50000000,50847000
2019,2395500,50187271,52582771
2020,4674651.79,50385163,55059815

Sample output:

2018,2019,2020
847000,2395500,4674651.79
50000000,50187271,50385163
50847000,52582771,55059815

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

While ( 
//Initialize variables...
[
Result = "" ; 
ColNo = "" ;
RowNo = "" ;
LiveRow = "" ;

Columns = PatternCount ( GetValue ( ArrayText ; 1 ) ; "," ) + 1 ;
Rows = ValueCount ( ArrayText )
] ;

RowNo ≠ ROWS or ColNo ≠ Columns ;   //The Continue condition...

//Loop logic...
[
Seperator = Case ( Result = "" ; "" ; RowNo = Rows ; "¶" ; "," ) ;
ColNo = Case ( RowNo = Rows ; ColNo + 1 ; ColNo = "" ; 1 ; ColNo ) ;
RowNo = Case ( RowNo = Rows ; 1 ; RowNo + 1 ) ;
LiveRow = Substitute ( GetValue ( ArrayText ; RowNo ) ; "," ; "¶" ) ;

Result = Result & Seperator & GetValue ( LiveRow ; ColNo )
] ;

Result
)

Uses a typical query result, useful for some charting techniques, virtual lists, reporting, etc.

Comments

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

PivotArray ( ArrayText )

Comments

Support this website.

Support this website.