Optimizing journey, summaziring events into one

Kalmer — Fri, 28 Sep 2018 16:52:08 GMT

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

Re: Optimizing journey, summaziring events into one

Kalmer — Fri, 28 Sep 2018 20:43:30 GMT

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;

topic Optimizing journey, summaziring events into one in App Development

Optimizing journey, summaziring events into one

Re: Optimizing journey, summaziring events into one