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

    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

       

      Thanks in advance.

        • How to find the smallest number in a string
          Vijay Kumar

          Hi You can use following script:

           

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

          LOAD 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
            Ashutosh Paliwal

            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

            • How to find the smallest number in a string
              Michael Solomovich

              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
                  Miguel Angel Baeyens de Arce

                  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

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

                  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