Another pivot question, based on min(field value)

[#1]

Hi All,

I want to figure out how to 1) pivot this data instead of it being inline and 2) base this pivot on the min value of the "line" field.  Here is an example of what the output is currently.

LOGID     STAFFTYPE                    STAFFNAME     LINE

12345      SCRUB                            Scrub, FName      1

12345      CIRCULATOR                 Circ, FName2        2

12345      CIRCULATOR                 Circ, FName1        3

12345      FELLOW                          Fellow, FName      4

12345      CIRCULATOR RELIEF    CircRel, FName     5

What I'd like it to display as would be:

I only want the Min(line) to display if it ever catches more than 1 of any of hte staff types.

LOGID     SCRUB            CIRCULATOR     CIRCULATOR RELIEF     FELLOW

12345      Scrub, FName  Circ, Fname2       CircRel, Fname                  Fellow, Fname

Is this possible in the LOAD script or possibly a extra step in the SQL or even something I can weed out when finally in-app?

All help is appreciated!

[jonathandienst]

Hi

Create a pivot table with LOGID and STAFFTYPE as dimensions, Only(STAFFNAME) as the expression. Expand the pivot table and drag the STAFFTYPE dimension to the top of the chart to make it horizontal.

If Only(STAFFNAME) returns null, then there may be more than one value of STAFFNAME at that point in the table. Use MinStrin(STAFFNAME) to get one value and CONCAT(DISTINCT STAFFNAME, ',') to get a list.

HTH

Jonathan

[#3]

That is getting me close, but the minstring() is giving me the wrong name back when applied to my actual data.  Looks like it pulls it alphabetically?  If for the one logid I am pulling in (for testing) i use maxstring() instead it pulls hte right name, which is why i am assuming alphabeitcal.