Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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);
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
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.
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
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