topic Another pivot question, based on min(field value) in QlikView

Another pivot question, based on min(field value)

Mon, 30 Jun 2014 14:34:45 GMT

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!

Re: Another pivot question, based on min(field value)

jonathandienst — Mon, 30 Jun 2014 14:56:05 GMT

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

Re: Another pivot question, based on min(field value)

Mon, 30 Jun 2014 15:13:35 GMT

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.