2 Replies Latest reply: Sep 25, 2016 6:34 PM by Marco Wedel RSS

    Subfield function

    Seth Rosenbauer

      I'm using the Subfield function to break up the flow of a customer. For example, the flow of a customer 123 is 1>2>3>7>3 and the flow of customer 456 is  2>4>5>1>3

       

      I'm using the subfield like this subfield(Flow, '>'). I want to keep them in the order of the flow and not in numerical order. I would like to have two columns. One for flow state and the other for customer ID. So it would be like below. However, I've done all of the above and created a table box, but the order of the flow isn't accurate, even when I update the flow property in the sort table to 'Load Order-Orginial'. Thoughts anyone?

      Customer IdFlow State

      123

      1
      1232
      1233
      1237
      1233
      4562
      4564
      4565
      4561
      4563
        • Re: Subfield function
          Jakub Szurogajło

          Hi Seth,

           

          Even though I do not quite understand, why sorting by "load order" does not work, I managed to display it correctly using a work around:

           

          [Table]:
          LOAD
          ID,
          subfield(Flow, '>') as [Flow State],
          Rowno() as [load order]
          INLINE [
              ID, Flow
              123, 1>2>3>7>3
              456, 2>4>5>1>3
          ];
          
          

           

          1. I then created a "Straight Table" chart

          2. Added all three dimensions to it

          3. Static expression (=1)

          4. Promoted load order in "Sort" tab, and sorted by numeric value

          5. In presentation tab hidden expression & "load order"

           

          Final table looks like that:

          Screen Shot 09-23-16 at 08.12 PM.PNG

           

          Hope it helps. App attached.

           

          BR,

          Kuba

          • Re: Subfield function
            Marco Wedel

            Hi,

             

            another possibility to create a sequence number could be:

             

            tabFlowState:
            LOAD CustomerID,
                SubField(Flow,'>',IterNo()) as FlowState,
                IterNo() as FlowSeq
            Resident tabFlow
            While IterNo()<=SubStringCount(Flow,'>')+1;
            

             

            A table box might not be appropriate to present your data because you have repeating flow states while table boxes only show distinct rows. Maybe a straight table could do instead?

             

            QlikCommunity_Thread_233951_Pic1.JPG

             

            QlikCommunity_Thread_233951_Pic2.JPG

             

            QlikCommunity_Thread_233951_Pic3.JPG

             

            QlikCommunity_Thread_233951_Pic4.JPG

            QlikCommunity_Thread_233951_Pic5.JPG

            QlikCommunity_Thread_233951_Pic6.JPG

             

            QlikCommunity_Thread_233951_Pic7.JPG

             

             

            hope this helps

             

            regards

             

            Marco