Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Subfield function

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
2 Replies
Not applicable

Re: Subfield function

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

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

Community Browser