Finding the first occurrence of a keyword after an occurrence of another keyword in a list of values in a column

[sharankv]

Hi Folks,

I want to find the difference between the order datetime for each order when there is an order for Department "Bags" for first time and followed by the first order in the Department "Apparels". Provided sample dataset below and also attached. The expected output for this dataset is given below. The answer for this question in both script level or chart level are welcomed.

Dataset:

Output:

Thanks in advance for your time and ansers.

[Pierrick · Accepted solution]

Hi @sharankv,

Here's a script solution to obtain a table equivalent to the one you want

//Find the first order for Bags by Order Number
NoConcatenate
TEMP:
Load
	[Order Number],
    Timestamp(min([Order Date])) as [First Order Date],
    'Bags' as [First Department]
Resident your_data
where [Department]='Bags'
group by [Order Number];

//Include orders for Apparels
left join(TEMP)
Load
	[Order Number],
    [Order Date] as [Second Order Date],
    'Apparels' as [Second Department]
Resident your_data
where [Department]='Apparels';

//Keep orders where Apparels>Bags and take the minimum, you can calculate your time interval at the same time.
NoConcatenate
newDATA:
Load
	[Order Number],
    [First Order Date],
    [First Department],
    Timestamp(min([Second Order Date])) as [Second Order Date],
    [Second Department],
    interval(Timestamp(min([Second Order Date]))-[First Order Date],'mm:ss') as [Time difference]
Resident TEMP
where [Second Order Date]>[First Order Date]
group by [Order Number],[First Order Date],[First Department],[Second Department];
drop table TEMP;

Pierrick