I would like to replace Nested "previous" (script function)

CarlosEduardoZ — Tue, 07 May 2024 21:33:41 GMT

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;

Re: I would like to replace Nested "previous" (script function)

MATC — Wed, 08 May 2024 09:53:28 GMT

You can try this solution

Tab2:
load distinct
TripStop,
if(len(trim(Source))=0, peek(Source), Source) as Source;
load distinct
Trip&Stop as TripStop,
Source
Resident Tab;

Re: I would like to replace Nested "previous" (script function)

CarlosEduardoZ — Wed, 08 May 2024 14:23:56 GMT

Thanks for your support.

I have learned that I could use the "Load" function more than one time when I'm loading data from a resident table 🙂

Thanks a lot!

topic Re: I would like to replace Nested "previous" (script function) in App Development

I would like to replace Nested "previous" (script function)

Re: I would like to replace Nested "previous" (script function)

Re: I would like to replace Nested "previous" (script function)