Dual aggregation displaying the string

[#1]

I have created a dual field and want to use aggregation total options on it (Min, Max, Avg) and display that Min as the text side of the dual.  Currently, I can not force the total row to display the text side of the dual.  How can I do this?

[rbecher]

Maybe try MinString() ? Or uplaod an example..

[#3]

MinString didn't work unfortunately.  There is one Table in the sample and the last field is the closest I can seem to get to an answer.  But I need the Total Row to display the text of the dual instead of the num of the dual ('Jan - Mar' in this case).

[mark_casselman · Accepted solution]

Hi Mark,

I did not expect it to work this way, but

max(dualfield)

returns the max number

and

maxString(dualfield)

return the text value of the row with the the max number.and not the row with the max string value (as I expected)

To get this the max string, you can use

MaxString(text(dualfield))

So the table:

table:

LOAD *, dual(value_text,value) as dualfield;

LOAD * INLINE [

    ID, value, value_text

    C1,1,A

    C2,2,X

    C3,3,B

];

gives:

Max(dualfield)=3

MaxString(dualfield)=B  (!!)

MaxString(text(dualfield)=X

Mark

[rbecher]

It's not a dual problem here. You comparing the wrong quarter. But, somewhere you need to calculate the quarter shifting:

QuarterMap:
MAPPING LOAD * INLINE [
    QuarterNum, QuarterDesc
    1, Apr - Jun
    2, Jul - Sep
    3, Oct - Dec
    4, Jan - Mar
];

MyData:
LOAD * INLINE [
    Quarter, Month
    1, Jan
    1, Feb
    1, Mar
    2, Apr
    2, May
    2, June
    3, July
    3, Aug
    3, Sep
    4, Oct
    4, Nov
    4, Dec
];

Calendar:
LOAD
    Month,
    dual(applymap('QuarterMap', Quarter), Quarter) AS Quarter_dual;
LOAD Month, if(Quarter=1, Quarter +3, Quarter -1) as Quarter
RESIDENT MyData;

- Ralf