Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Maybe try MinString() ? Or uplaod an example..
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).
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
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