Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
ziabobaz
Contributor II

Find second occurrence of record BY dimension

Hello,

I have Source Table:

 Screenshot_7.jpg

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

Screenshot_8.jpg

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)
1 Solution

Accepted Solutions
Partner
Partner

Re: Find second occurrence of record BY dimension

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

Work smarter, not harder
8 Replies
Partner
Partner

Re: Find second occurrence of record BY dimension

do as follow:

Load 

Partner,

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

resident urTable group by Partner;

 

Channa
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
Partner

Re: Find second occurrence of record BY dimension

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

Work smarter, not harder
ziabobaz
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

 

ziabobaz
Contributor II

Re: Find second occurrence of record BY dimension

Hey, that works!

thank you

ziabobaz
Contributor II

Re: Find second occurrence of record BY dimension

Hi again

 

I am actually having trouble computing this:

Screenshot_9.jpg

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
Valued Contributor III

Re: Find second occurrence of record BY dimension

try to add RowNum()

 

take (ROWNUM) Group by partner

later add rank on rownNumber for partner 

take  rank ❤️

just an idea

try

 

 

Channa
ziabobaz
Contributor II

Re: Find second occurrence of record BY dimension

Should have used Peek instead of Previous
works now!