# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Contributor II

## Find second occurrence of record BY dimension

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!

Labels (1)
• ### second occurrence

1 Solution

Accepted Solutions
Partner

## Re: Find second occurrence of record BY dimension

Hi Ziabobaz,

Try this, I made an Inline Table as example.

```Table:
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
Partner,
Data - PreviousData as DaysBetween
Where not isnull(PreviousData)
;
*,
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

Work smarter, not harder
8 Replies
Partner

## Re: Find second occurrence of record BY dimension

do as follow:

Partner,

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

resident urTable group by Partner;

Valued Contributor III

## Re: Find second occurrence of record BY dimension

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

Channa
Partner

## Re: Find second occurrence of record BY dimension

Hi Ziabobaz,

Try this, I made an Inline Table as example.

```Table:
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
Partner,
Data - PreviousData as DaysBetween
Where not isnull(PreviousData)
;
*,
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

Work smarter, not harder
Contributor II

## Re: Find second occurrence of record BY dimension

Guys, thank you

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

I need time Between First and Second

Contributor II

Hey, that works!

thank you

Contributor II

## Re: Find second occurrence of record BY dimension

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)

Valued Contributor III

## Re: Find second occurrence of record BY dimension

take (ROWNUM) Group by partner

later add rank on rownNumber for partner

take  rank ❤️

just an idea

try

Channa
Contributor II

## Re: Find second occurrence of record BY dimension

Should have used Peek instead of Previous
works now!