How to find the smallest number in a string

[#1]

Hello

I have a list of unique keys and a string of numbers separated by '~'. 

Uniquekey	VisitWeek
1002122	16.2~14.1~20.3~22.2~27.2~30.2~24.3~32.2~36.1~34.2~37.2~38.1~39.1~40.4~40.2
1003818	16~20.6~27~29~33~35~35.3~35.2~35.6
1000186	31.5~33~18.3~13~23.2~13.3~20.5~27.4~28.4~36.4
1006542	19.5~22~30~28
1016978	40~41.1~25~28~30~35~36~36~38~39

I need to locate the smallest number in the string (like below).  Is it possible?

Uniquekey	VisitWeek
1002122	14.1
1003818	16
1000186	13
1006542	19.5
1016978	25

Thanks in advance.

[vijay_iitkgp]

Hi You can use following script:

T1:
Load
Uniquekey,
Min(Visitweek)
Group By Uniquekey;

LOAD Uniquekey,
     Subfield(VisitWeek,'~') as Visitweek
FROM

(ooxml, embedded labels, table is Sheet1);

[#3]

Hi,

If your unique key is going to have only one visit week entry then above will do fine but if it is not then you can add visitweek also in group by.

So, like this way, you can do. I have taken your table as inline table, so used resident. Just try this.

T1:

LOAD * INLINE [

   UniqueKey, VisitWeek

    1002122, 16.2~14.1~20.3~22.2~27.2~30.2~24.3~32.2~36.1~34.2~37.2~38.1~39.1~40.4~40.2

    1003818, 16~20.6~27~29~33~35~35.3~35.2~35.6

    1000186, 31.5~33~18.3~13~23.2~13.3~20.5~27.4~28.4~36.4

    1006542, 19.5~22~30~28

    1016978, 40~41.1~25~28~30~35~36~36~38~39

];

T2:

LOAD

UniqueKey,

VisitWeek,

min(VisitWeek_temp) as Min_VisitWeek Group by UniqueKey,VisitWeek;

LOAD

UniqueKey,

SubField(VisitWeek,'~') as VisitWeek_temp,

VisitWeek

Resident T1;

DROP Table T1;

Hope this helps.

Regards,

Ashutosh

[Anonymous]

If you want do it on the front end, in chart expression, function rangemin() is what you need.  Just replace the '-' with comma in the strings - you can use replace() function for this.

[Miguel_Angel_Baeyens]

Hi,

Elaborating Michael's suggestion, the following expression should work either in the script or in the front end, returning a table with a new field "MinPerUniquekey" with the minimum value of VisitWeek separated by "~" for each Uniquekey. The RangeMin() should work as an expression in a chart with Uniquekey as dimension.

DataTemp:
LOAD * INLINE [
Uniquekey, VisitWeek
1002122, 16.2~14.1~20.3~22.2~27.2~30.2~24.3~32.2~36.1~34.2~37.2~38.1~39.1~40.4~40.2
1003818, 16~20.6~27~29~33~35~35.3~35.2~35.6
1000186, 31.5~33~18.3~13~23.2~13.3~20.5~27.4~28.4~36.4
1006542, 19.5~22~30~28
1016978, 40~41.1~25~28~30~35~36~36~38~39
];
MinTemp:
LEFT JOIN (DataTemp) LOAD Uniquekey,
     RangeMin(SubField(Concat(VisitWeek, ','), '~')) AS MinPerUniquekey
RESIDENT DataTemp
GROUP BY Uniquekey;

Hope that helps.

Miguel

[#6 · Accepted solution]

Thank you for your suggested answers. 

I have used Min(SubField(VisitWeek,'~')) in my expression to get the answer.  It works perfectly fine in expression.  When I have tried to use this in dimension and it didn't work though.

Cheers

ptsang1011