Hi,
I'd like to replace in my script some nested previous used to find the last value for a field (in this case, the supply source) in a table that have values similar to:
Tab:
Load *,
If(len(trim(Source2))= 0 or Source2='NULL' or Source2='-', Null(), Source2 ) as Source
INLINE [Trip Stop Type Source2 Pickup Delivered
a1 1 1 a 20 -
a1 2 2 - - 11
a1 3 2 - - 6
a1 4 2 - - 2
a1 5 3 b 18 -
a1 6 2 - - 16
a1 7 4 - - 3
](delimiter is ' ');
This would be a tipical trip, in which there are 4 types of Stops: 1 for initial product pickup, 2 for delivery to customers, 3 for others product pickup (e.g. in a provider) and 4 for product returned to plant.
I need to identify the source for each stop (that would be the previous source untill the truck stop in a provider, changing the source, type 3) and I solve this situation by using nested "previous" function as follow. Could you help me to simplify this scrip? (maybe with peek function or using other one).
My initial solution was:
Source:
Load
Trip&Stop as TripStop,
if(isNull(Source),
if(isnull(Previous(Source)),
if(isnull(previous(Previous(Source))),
if(isnull(previous(previous(Previous(Source)))),
if(isnull(previous(previous(previous(Previous(Source))))),
if(isnull(previous(previous(previous(Previous(previous(Source)))))),
if(isnull(previous(previous(previous(Previous(previous(previous(Source))))))),
if(isnull(previous(previous(previous(Previous(previous(previous(previous(Source)))))))),
if(isnull(previous(previous(previous(Previous(previous(previous(previous(previous(Source))))))))),
if(isnull(previous(previous(previous(Previous(previous(previous(previous(previous(previous(Source)))))))))),
if(isnull(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(Source))))))))))),
if(isnull(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(Source)))))))))))),
if(isnull(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(previous(Source))))))))))))),
if(isnull(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(Source)))))))))))))),
if(isnull(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(Source))))))))))))))),
if(isnull(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(Source)))))))))))))))),
if(isnull(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(Source))))))))))))))))),
if(isnull(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(Source)))))))))))))))))),
if(isnull(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(Source))))))))))))))))))),
previous(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(Source))))))))))))))))))),
previous(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(Source)))))))))))))))))))
,previous(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(Source))))))))))))))))))
,previous(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(Source)))))))))))))))))
,previous(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(Source))))))))))))))))
,previous(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(previous(previous(Source)))))))))))))))
,previous(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(previous(Source))))))))))))))
,previous(previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(Source)))))))))))))
,previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(previous(Source))))))))))))
,previous(previous(previous(Previous(previous(previous(previous(previous(previous(previous(Source)))))))))))
,previous(previous(previous(Previous(previous(previous(previous(previous(previous(Source))))))))))
,previous(previous(previous(Previous(previous(previous(previous(previous(Source)))))))))
,previous(previous(previous(Previous(previous(previous(previous(Source))))))))
,previous(previous(previous(Previous(previous(previous(Source)))))))
,previous(previous(previous(Previous(previous(Source))))))
,previous(previous(previous(Previous(Source)))))
,previous(previous(Previous(Source))))
,previous(Previous(Source)))
,Previous(Source))
,Source) as SourceOk
RESIDENT Tab;
DROP TABLE Tab;
