8 Replies Latest reply: Dec 21, 2015 9:18 AM by Saad Alam RSS

    Loop with customized decimal increment

      Hi,

       

          This is a sample data set.

      Storesminmax
      1240243
      289.189.17

       

      I want to create a continuum of values ranging from min to max for each store. So for Store 1, the values will be 240, 241, 242, 243. And for store 2, they will be 89.10, 89,11, 89.12,,....., 89.17.

       

      I am writing a loop. However, the increments are in integers. How can I force the loop to increase by 0.01 for stores whose minimums are left in decimals? Here's my current code:

       

      LET vNumRows = NoOfRows('C'); //// C is a previously created table

      For Counter = 1 TO $(vNumRows)

      let vstoreLkup = Peek('store',$(Counter)-1,C);

      let vmin = Peek('min',$(Counter)-1,C);

      let vmax = Peek('max',$(Counter)-1,C);

       

      IF vmin=round(output) THEN  ////// output field is original. Some stores (like store 1) need to be rounded to get the min. Others (store 2)                                                             are left in  decimals

        For vcontinuum = $(vmin) to $(vmax)

        Concatenate(Data)  

        load

        '$(vstoreLkup)' as store,

        '$(vcontinuum)' as continuum,

        '$(vmin)' as  min,

        '$(vmax)' as max,

        AutoGenerate 1;

      next

      ELSE

           For vslider = $(vmax) to $(vmin)    /////Here is where I need to force the increments to be in decimal, I am thinking.

              Concatenate(Data)

              load 

        Concatenate(Data)  

        load

        '$(vstoreLkup)' as store,

        '$(vcontinuum)' as continuum,

        '$(vmin)' as  min,

        '$(vmax)' as max,

        AutoGenerate 1;

      next

      ENDIF

      next

       

       

      Appreciate the help. Thank you.

       

      Saad

        • Re: Loop with customized decimal increment
          Christian Wolf

          What about using a macro like;

           

          Function getContinuum (min,max)

            b=false

            s=min & ";"

            if( int(min)<>min or int(max)<>max) then

              min = min * 100

              max = max * 100

              b=true

            end if

            do while min<max

              min = min +1

              if b then

                s=s & min/100 & ";"

              else

                s=s & min & ";"

              end if

            loop

            getContinuum=left(s,len(s)-1)

          end function

           

           

          Then in script:

           

          LOAD

          *,

          getContinuum(min,max) as continuum

          Resident C;

            • Re: Loop with customized decimal increment
              Christian Wolf

              or a little bit better:

               

              Function getContinuum(min,max)

                  b=false

                  s=min & ";"

                  if( int(min)<>min or int(max)<>max) then

                      b=true

                  end if

                  do while min<max

                      if b then

                          min = min + 0.01

                      else

                          min = min + 1

                      end if

                      s= s & min & ";"

                  loop

                  getContinuum=left(s,len(s)-1)

              end function

            • Re: Loop with customized decimal increment
              Sinan Ozdemir

              Hi,

               

              One approach could be using While in the load script:

              Capture.PNG

              And here is the data model:

              Capture.PNG

              I am also attaching the qvw.

               

              Hope this helps.

              • Re: Loop with customized decimal increment
                Marco Wedel

                Hi,

                 

                a simpler solution might be:

                 

                QlikCommunity_Thread_194232_Pic1.JPG

                 

                LOAD *,
                    min+If(Frac(min),0.01,1)*(IterNo()-1) as continuum
                FROM [https://community.qlik.com/thread/194232] (html, codepage is 1252, embedded labels, table is @1)
                While min+If(Frac(min),0.01,1)*(IterNo()-1)<=max;
                

                 

                 

                hope this helps

                 

                regards

                 

                Marco

                  • Re: Loop with customized decimal increment

                    Hi Marco,

                     

                        Thank you again for your suggestion on the while loop. As I mentioned earlier it did work. But upon further validation, I found that for some stores, the continuum does not reach the max value, even using the same logic. I have attached my qvw. Max from both stores is 0.54. But for store #1, continuum does not reach the max value. It stops at 0.55. Store #2 works as it should. In this case, lower is better. So I am subtracting .01 from the min value each time until it reaches the max.

                     

                      It seems that the min value for store 1 (ceil(0.70395633,.01)= .71) is the problem. Because if you change the min to max and do while loop with .01 increment, for store 1, continuum goes up to only 0.7. This code is commented in the qvw.

                     

                      Also, if you look at the continuum values, sometimes it is creating two sets of continuum values over the same range for each of the stores.

                     

                      Any help or explanation would be greatly appreciated. I am totally stumped as to why this would not be working. Thank you in advance.

                  • Re: Loop with customized decimal increment

                    Thank you everyone for your helpful responses. Using while solved the issue perfectly.

                     

                    Regards,

                    Saad