topic Re: Subfield function in QlikView

Subfield function

Fri, 23 Sep 2016 12:31:40 GMT

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 Id	Flow State
123	1
123	2
123	3
123	7
123	3
456	2
456	4
456	5
456	1
456	3

Re: Subfield function

Fri, 23 Sep 2016 18:13:35 GMT

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:

Hope it helps. App attached.

BR,

Kuba

Re: Subfield function

MarcoWedel — Sun, 25 Sep 2016 22:34:50 GMT

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?

hope this helps

regards

Marco