topic Re: Dual aggregation displaying the string in QlikView

Dual aggregation displaying the string

Mon, 18 Nov 2013 21:17:31 GMT

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?

Re: Dual aggregation displaying the string

rbecher — Mon, 18 Nov 2013 22:18:11 GMT

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

Re: Re: Dual aggregation displaying the string

Mon, 18 Nov 2013 22:44:30 GMT

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).

Re: Dual aggregation displaying the string

mark_casselman — Mon, 18 Nov 2013 23:24:21 GMT

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

Re: Re: Re: Dual aggregation displaying the string

rbecher — Tue, 19 Nov 2013 09:32:03 GMT

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