- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Max Value of a Field (Not all data in the dataset; Just one cell
Let's say I have this table
Import:
LOAD * Inline
[
duration
()
(03,238,09,)
(10,361,04,)
(163)
(116)
(04,172,07,)
];
Dataset:
NoConcatenate
LOAD *
//Max(duration) as MaxValue
Resident Import;
DROP Table Import;
I want the table to look like this:
duration | MaxValue |
() | 0 |
(03,238,09,) | 238 |
(10,361,04,) | 361 |
(163) | 163 |
(116) | 116 |
(04,172,07,) | 172 |
How do I find the max value of a string of one cell? Not the entire set of data. I have tried Max() and MaxString() but as you know, it finds the Max of the entire data set for that field.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If you have few values in the same cell you can use subfield
In your example
RANGEMAX(
SUBFIELD(PurgeChar(duration, '()'), ',', 1),
SUBFIELD(PurgeChar(duration, '()'), ',', 2),
SUBFIELD(PurgeChar(duration, '()'), ',', 3)
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If you have few values in the same cell you can use subfield
In your example
RANGEMAX(
SUBFIELD(PurgeChar(duration, '()'), ',', 1),
SUBFIELD(PurgeChar(duration, '()'), ',', 2),
SUBFIELD(PurgeChar(duration, '()'), ',', 3)
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@mp802377 wrote:
Let's say I have this table
Import:
LOAD * Inline
[
duration
()
(03,238,09,)
(10,361,04,)
(163)
(116)
(04,172,07,)];
Dataset:
NoConcatenate
LOAD *
//Max(duration) as MaxValue
Resident Import;DROP Table Import;
I want the table to look like this:
duration MaxValue () 0 (03,238,09,) 238 (10,361,04,) 361 (163) 163 (116) 116 (04,172,07,) 172
How do I find the max value of a string of one cell? Not the entire set of data. I have tried Max() and MaxString() but as you know, it finds the Max of the entire data set for that field.
The max function works based on the group by clause at the end, if you have a column against which you need to find out the max value. Also, the above syntax seems to be incomplete without the group by clause, and after getting the max value you can right join the data against which you need to get the data.
For example
Load
max (duration)
Resident Table1
Group by key;