5 Replies Latest reply: Jan 16, 2012 8:08 PM by ptsang1011

# How to find the smallest number in a string

Hello

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

UniquekeyVisitWeek
100212216.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
100381816~20.6~27~29~33~35~35.3~35.2~35.6
100018631.5~33~18.3~13~23.2~13.3~20.5~27.4~28.4~36.4
100654219.5~22~30~28
101697840~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?

UniquekeyVisitWeek
100212214.1
100381816
100018613
100654219.5
101697825

• ###### How to find the smallest number in a string

Hi You can use following script:

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

Subfield(VisitWeek,'~') as Visitweek
FROM
[C:\Documents and Settings\kumarvijay\Desktop\Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

• ###### How to find the smallest number in a string

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:

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:

UniqueKey,

VisitWeek,

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

UniqueKey,

SubField(VisitWeek,'~') as VisitWeek_temp,

VisitWeek

Resident T1;

DROP Table T1;

Hope this helps.

Regards,

Ashutosh

• ###### How to find the smallest number in a string

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.

• ###### Re: How to find the smallest number in a string

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:
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:
RangeMin(SubField(Concat(VisitWeek, ','), '~')) AS MinPerUniquekey
RESIDENT DataTemp
GROUP BY Uniquekey;
```

Hope that helps.

Miguel