Find second occurrence of record BY dimension

[ziabobaz]

Hello,

I have Source Table:

 

I need to find number of days between First and Second order, BY Partner:

I can do it in number of iterations:

- number each Order occurrence with if(partner=above(partner), OrderNum=above(orderNum)-1) as OrderNum

- load only 1 and 2 records in the interim table with Load * Where OrderNum<3

- unpivot the table so i have two dates for the 1 and 2nd order correspond to each Patner and subsequently find difference between two dates 

Is there a simplier way to do it?

Thank you!

[OmarBenSalem]

do as follow:

Load 

Partner,

(max(Order)-min(Order))-1  as DayBetween

resident urTable group by Partner;

[Channa]

Try to make query

Partner , Min(OrderNumber),Min(Orderdate), Max(OrderNumber),max(orderDate)group by Partner

later you can take time difference

may be it will help

[JordyWegman · Accepted solution]

Hi Ziabobaz,

Try this, I made an Inline Table as example.

Table:
Load * Inline [
Partner, Data, OrderNum
baz, 02-01-2018, 2
baz, 01-01-2018, 1
baz, 03-01-2018, 3
trol, 05-02-2018, 2
trol, 05-02-2017, 1
trol, 05-03-2018, 3
]
;

Table2:
NoConcatenate
Load
	Partner,
    Data - PreviousData as DaysBetween
Where not isnull(PreviousData)
;
Load
	*,
    IF(Partner = Previous(Partner) and OrderNum = Previous(OrderNum)+1, Previous(Data)) as PreviousData
Resident Table
Where OrderNum < 3
Order by Partner, OrderNum
;

Drop table Table;

Jordy

Climber

[ziabobaz]

Guys, thank you

But this will give me the time between First and Last orders

I need time Between First and Second

[ziabobaz]

Hey, that works!

thank you

[ziabobaz]

Hi again

I am actually having trouble computing this:

I can't use 

if(previous(partner)=partner, OrderNum=1+previous(orderNum)

..because i can't refer a field to itself while it is not loaded yet (OrderNum)

[Channa]

try to add RowNum()

take (ROWNUM) Group by partner

later add rank on rownNumber for partner 

take  rank ❤️

just an idea

try

[ziabobaz]

Should have used Peek instead of Previous
works now!