Subselect in load script

bean — Wed, 17 Jul 2024 14:32:00 GMT

Hi there

While loading my table, I would like to establish an subselect on the same data source.

Entry

Timestamp

EventId

PartnerEventId

TimestampPartner

2569

10.06.2024 00:40:00.00

2570

10.06.2024 00:56:53.53

2571

10.06.2024 01:25:00.00

10.06.2024 00:40:00.00

2572

10.06.2024 01:39:33.33

2573

10.06.2024 02:15:57.57

When e.g. loading line 2571 I would like to lookup the TimestampPartner according to PartnerEventId = EventId and having the next older timestamp.

A "classic" SQL statement could look like:
SELECT LIMIT 1
timestamp
FROM table t
WHERE t.timestamp < timestamp AND PartnerId = t.EventId
ORDER BY t.Timestamp DESC

Any suggestions are welcome. Thanks!

Re: Subselect in load script

morgankejerhag — Wed, 12 Feb 2025 21:16:03 GMT

I expanded the example a bit to have multiple partner events that we should find the nearest, earlier timestamp for. Did I understand the problem correctly?

DataIn:

Load * INLINE [

Entry, Timestamp, EventId, PartnerEventId

2566, 2024-06-10 00:25:00.00,7,15

2567, 2024-06-10 00:30:00.00,7,15

2568, 2024-06-10 00:35:00.00,5,15

2569, 2024-06-10 00:40:00.00,5,15

2570, 2024-06-10 00:56:53.53,3,7

2571, 2024-06-10 01:25:00.00,12,5

2572, 2024-06-10 01:39:33.33,3,8

2573, 2024-06-10 02:15:57.57,5,15

];

DataJoined:

noconcatenate Load * resident DataIn;

left join

Load

EventId as PartnerEventId,

Timestamp as PartnerTimestamp

resident DataIn;

drop table DataIn;

DataFiltered:

NoConcatenate Load

if(peek(EventId)<>EventId,1,peek(Rank)+1) as Rank

resident DataJoined where PartnerTimestamp < Timestamp order by Entry,PartnerTimestamp desc;

drop table DataJoined;

Data:

NoConcatenate Load * resident DataFiltered where Rank=1;

drop table DataFiltered;

drop field Rank;

topic Subselect in load script in App Development

Subselect in load script

Re: Subselect in load script