topic Re: Load distinct routes and then route steps using subfield in App Development

Load distinct routes and then route steps using subfield

SDT — Wed, 23 Jun 2021 12:33:32 GMT

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.

Re: Load distinct routes and then route steps using subfield

sunny_talwar — Wed, 23 Jun 2021 12:40:31 GMT

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;

Re: Load distinct routes and then route steps using subfield

SDT — Wed, 23 Jun 2021 12:45:15 GMT

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.

Re: Load distinct routes and then route steps using subfield

sunny_talwar — Wed, 23 Jun 2021 12:52:31 GMT

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;

Re: Load distinct routes and then route steps using subfield

SDT — Wed, 23 Jun 2021 14:20:13 GMT

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];

Re: Load distinct routes and then route steps using subfield

SDT — Wed, 23 Jun 2021 14:47:41 GMT

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;

Re: Load distinct routes and then route steps using subfield

SDT — Wed, 23 Jun 2021 15:51:54 GMT

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];

Re: Load distinct routes and then route steps using subfield

terry_7896 — Thu, 24 Jun 2021 05:29:22 GMT

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.