3 Replies Latest reply: Jan 6, 2016 7:42 PM by Marco Wedel RSS

    Generate values

    Andrew Nicholls

      Hello,

       

      Is it possible to create a field that generates values? I have two fields one called first order and another called last order. It is possible to generate the numbers in between so it would look like the below?

       

      First OrderLast OrderOrders
      151
      152
      153
      154
      155

       

      I'm unsure on how it can be done? Does anyone have an idea?

        • Re: Generate values
          Sunny Talwar

          Try this:

           

          Table:

          LOAD [First Order],

              [Last Order],

              [First Order] + IterNo() - 1 as Order

          While [First Order] + IterNo() - 1 <= [Last Order];

          LOAD * Inline [

          First Order, Last Order

          1, 5

          ];


          Capture.PNG

           

          • Re: Generate values
            Ralf Becher

            Hi Andrew,

             

            I assume you need this on a client level (or other aggregation):

             

            Orders:
            LOAD * INLINE [
                ClientID, OrderID
                A, 3
                A, 4
                A, 5
                A, 6
                A, 7
                B, 2
                B, 3
                B, 4
            ];
            
            JOIN
            LOAD ClientID, min(OrderID) as FirstOrderID, max(OrderID) as LastOrderID
            Resident Orders
            Group By ClientID;
            ;
            
            

             

            - Ralf

            • Re: Generate values
              Marco Wedel

              Hi,

               

              if you don't want to change the row number of your initial table you could create separate order (maybe with additional order facts?) and link tables:

               

              QlikCommunity_Thread_198623_Pic1.JPG

               

              QlikCommunity_Thread_198623_Pic2.JPG

               

              table1:
              LOAD *,
                  AutoNumberHash128([First Order], [Last Order]) as %Key
              INLINE [
                  First Order, Last Order
                  1, 5
                  2, 4
                  3, 8
                  4, 10
              ];
              
              tabOrders:
              LOAD IterNo() as Orders,
                  Ceil(Rand()*100) as someOrderFact1,
                  Ceil(Rand()*100) as someOrderFact2,
                  Ceil(Rand()*100) as someOrderFact3
              While IterNo()<=MaxOrder;
              LOAD Max([Last Order]) as MaxOrder
              Resident table1;
              
              tabLink:
              IntervalMatch(Orders)
              LOAD Distinct
                  [First Order], [Last Order]
              Resident table1;
              
              Join (tabLink)
              LOAD Distinct
                  [First Order], [Last Order],
                  AutoNumberHash128([First Order], [Last Order]) as %Key
              Resident tabLink;
              
              DROP Fields [First Order], [Last Order] From tabLink;
              

               

              hope this helps

               

              regards

               

              Marco