Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.
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;
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];
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;
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];
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.