Optimizing journey, summaziring events into one

[Kalmer]

Hello!

I'm facing a tricky task i need to solve. I need to solve or optimize the "journey"

I have uploaded an example data and the desired result in an excel format.

The Rows E,F,L,M are just Total rows (no need to read them in document)

Thank you

[Kalmer · Accepted solution]

Out of boredom managed to fix it myself. Here's the solution if anyone was wondering:
(Based on excel)

Mastertable:

LOAD

    'Ping' as Fact,

    rowNo() as NrOfEvents,

    CardID,

    LocationID,

    Timestamp(Timestamp#("Timestamp",'DD.MM.YYYY hh:mm')) as "Timestamp"

FROM [lib://Desktop/WhatiWant.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1)

where len(CardID)> 1;

Concatenate(Mastertable)

LOAD

    'Temp Result' as Fact,

    rowNo() as NrOfEvents,

    CardID1 as CardID,

    LocationID1 as LocationID,

    Timestamp(Timestamp#(left("Timestamp range:", 16))) as "Timestamp",

    "Timestamp range:" as "Timestamp range",

    "Nr of events1" as "Nr of events"

FROM [lib://Desktop/WhatiWant.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1)

where len(CardID1)> 1;

// Journey from multiple same level events to one

tmp1:

load distinct

    CardID as CardID1,

    LocationID as LocationID1,

    if(previous(CardID) = CardID, previous(LocationID), null()) as PreviousLocationID,

    Timestamp as Timestamp1

Resident Mastertable

Where Fact = 'Ping'

order by CardID, Timestamp asc

;

NoConcatenate

tmp2:

load

    *,

    if(previous(CardID1) = CardID1, peek(Timestamp1), Timestamp1) as NextTimestamp

where LocationID1 = NextLocationID or LocationID1 = PreviousLocationID or PreviousLocationID = null() or NextLocationID = null()

;

load

    CardID1,

    LocationID1,

    PreviousLocationID,

    if(previous(CardID1) = CardID1, previous(LocationID1), null()) as NextLocationID,

    Timestamp1

Resident tmp1

order by CardID1, Timestamp1 desc

;

NoConcatenate

tmp3:

load

    *,

    if(previous(CardID1) = CardID1, previous(Timestamp1), Timestamp1) as PreviousTimestamp,

    if(Previous(CardID1) = CardID1 and previous(LocationID1) = LocationID1, if(isNull(Peek('Step')), 1, Peek('Step')),

    if(Previous(CardID1) <> CardID1, 1,

    rangesum(peek('Step'),1))) AS Step

resident tmp2

order by CardID1, Timestamp1 asc

;

Concatenate(Mastertable)

Load

    'Result' as Fact,

    CardID1 as CardID,

    LocationID1 as LocationID,

    MinAeg as "Timestamp",

    MaxAeg as "MaxTimestamp",

    Interval(MaxAeg - MinAeg,'hh:mm') as AvgVisitShopTime,

    Step

;

load

    CardID1,

    Timestamp(min(Timestamp1)) as MinAeg,

    Timestamp(max(NextTimestamp)) as MaxAeg,

    LocationID1,

    Step

Resident tmp3

group by CardID1, Step, LocationID1

;

drop table tmp1, tmp2, tmp3;