topic Re: How to pick up the second date from the data in QlikView

How to pick up the second date from the data

udaya_kumar — Wed, 31 Jan 2018 07:06:46 GMT

Hi,

I have data like this

Ticket	Task	Created Date	Last modified Date	Created By
1	1	1-Jan-2017 10:00	1-Jan-2017 10:15	Admin
1	2	1-Jan-2017 12:00	1-Jan-2017 12:05	Agent
1	3	2-Jan-2017 10:05	2-Jan-2017 10:06	Agent
2	4	1-Jan-2017 11:00	1-Jan-2017 11:15	Admin
2	5	1-Jan-2017 13:00	1:Jan-2017 13:05	Agent
2	6	2-Jan-2017 10:05	2-Jan-2017 10:06	Agent
3	7	2-Jan-2017 10:05	2-Jan-2017 10:05	Admin
3	8	2-Jan-2017 10:05	2-Jan-2017 10:05	Agent

Here the data is grouped by Ticket, i have to pick the start date and end date from this data.

Start date will be the Created Date (which is created by Admin)

For example, Ticket = 1, the Start Date is 1-Jan-2017 10:00 (Task created by Admin)

End date will be the Last Modified Date (The Last modified date after the first task (Admin task) should be considered).

For example, Ticket = 1, The end date is 1-Jan-2017 12:05.

My question is, How to pick up the last modified date from the date which is the task created (Task after the admin task, i.e., task created by some agent) after the first task (Which is created by admin)

Re: How to pick up the second date from the data

kaushiknsolanki — Wed, 31 Jan 2018 07:19:50 GMT

Try this,

Data:

Load Ticket,Min(CreatedDate) as MinDate

From xyz

Where Createdby='Admin'

Group by Ticket;

Left join

Load Ticket,Max(LastModifiedDate) as MaxDate

From xyz

Where Createdby<>'Admin'

Group by Ticket;

Regards,

Kaushik Solanki

Re: How to pick up the second date from the data

udaya_kumar — Wed, 31 Jan 2018 09:30:39 GMT

Hi,

If i use the below code, then it will take the last row date in the last modified date field group by Ticket right,

But i want the row after the admin task date to be picked up in the script.

Left join

Load Ticket,Max(LastModifiedDate) as MaxDate

From xyz

Where Createdby<>'Admin'

Group by Ticket;

Re: How to pick up the second date from the data

kaushiknsolanki — Wed, 31 Jan 2018 09:34:20 GMT

Thats fine,

In such case you can use the Min function, which will take the immediate next row after Admin row.

Left join

Load Ticket,Min(LastModifiedDate) as NextDate

From xyz

Where Createdby<>'Admin'

Group by Ticket;

Regards,

Kaushik Solanki