Load distinct routes and then route steps using subfield

[SDT]

Hello.

My data has routing information stored in a CSV string such as "A,B,E,F,G" for every order. I would like to create a table of distinct routes (easy enough with load distinct) and then break down the route steps and number them based on order. There is also the possibility a step will be repeated such as "A,B,A,A,G".

I tried this:

LOAD DISTINCT

Route,

Subfield(Route,',') as RouteStep

RESIDENT MyData;

Due to distinct it only loads each type of routing once, regardless of how many times it occurs in the route. I also don't know how to add the step number. For example, if the route is "A,B,D,G,X" then the record with that route and "X" as the route step should have a StepNumber of 5.

Any help is appreciated. I'm sure this is not difficult but 20 minutes of googling did not yield a usable result for me.

[sunny_talwar]

May be do it in two steps?

Step 1:

tmp:
LOAD DISTINCT
Route
RESIDENT MyData;

Step 2:

Final:
LOAD Route,
     Subfield(Route,',') as RouteStep
Resident tmp;

[SDT]

Thank you Sunny.

I think that would work (could it be accomplished with a previous load also?). I would still need to add something to indicate the RouteStep position in the routing though.

[sunny_talwar]

Try this

Table:
LOAD * INLINE [
    Route
    "A,B,E,F,G"
    "A,B,A,A,G"
    "A,B,E,F,G"
];

Final:
LOAD DISTINCT *,
	 If(Route = Previous(Route), RangeSum(Peek('RowNo'), 1), 1) as RowNo;
LOAD Route,
	 Subfield(Route,',') as RouteStep
RESIDENT Table;

DROP Table Table;

[SDT]

Still not giving the intended result and I think it has to do with not getting distinct order routes loaded. The maximum number of steps in any of the routes is 9 and I have RouteStepNum values up to 36.

[OrderRouteBreakdown]:
LOAD DISTINCT
OrderRoute,
OrderRouteStep,
IF(OrderRoute=Previous(OrderRoute), Rangesum(Peek('OrderRouteStepNum'), 1), 1) as OrderRouteStepNum;
LOAD
OrderRoute,
TRIM(Subfield(OrderRoute,',')) as OrderRouteStep
RESIDENT [OrderDueDates];

[SDT]

Fixed it. I think the Rangesum was the problem. I also split it into two and it is working now. THANK YOU!

[RouteTemp]:
LOAD DISTINCT
OrderRoute
RESIDENT OrderDueDates;

[OrderRouteBreakdown]:
LOAD
OrderRoute,
Subfield(OrderRoute,',') as OrderRouteStep,
IF(OrderRoute=Previous(OrderRoute), Peek('OrderRouteStepNum')+1, 1) as OrderRouteStepNum
RESIDENT [RouteTemp];

DROP TABLE RouteTemp;

[SDT]

Not quite right. But this is:

[OrderRoutes]:
LOAD DISTINCT
OrderRoute,
SUBstringcount(OrderRoute,'PS')&'PS '&SUBStringCount(OrderRoute,'TC')&'TC ' as RouteCoatings,
substringcount(OrderRoute,',')+1 as OrderRouteSteps
RESIDENT Orders;

[OrderRouteBreakdown]:
NOCONCATENATE LOAD
OrderRoute,
Subfield(OrderRoute,',') as OrderRouteStep,
IF(OrderRoute=Peek('OrderRoute'), Peek('OrderRouteStepNum')+1, 1) as OrderRouteStepNum
RESIDENT [OrderRoutes];

[terry_7896]

the IETF ROLL working Whereas the OF dictates rules such as DODAG parent selection​, load  Nodes (B) and (C) do not process DIO messages from nodes deeper than The subfields are ordered by preference, with PC1 being the most preferred.