Skip to main content
Announcements
Join us on Feb.12 to Discover what’s possible with embedded analytics: REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
bean
Contributor III
Contributor III

Subselect in load script

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
 

 

5
15  
2570 10.06.2024 00:56:53.53 3 7  
2571 10.06.2024 01:25:00.00 12 5 10.06.2024 00:40:00.00
2572 10.06.2024 01:39:33.33 3 7  
2573 10.06.2024 02:15:57.57 5 15  

 

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!

 

Labels (2)
1 Reply
morgankejerhag
Partner - Creator III
Partner - Creator III

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;