Need help

[madhudinesh]

Hi All,

I have the data as below (#1) in a table currently, where the Segment date is expected to be displayed as shown in table (#2). From the sample data given below, the application is approved four times, then denied, then approved and again denied for a particular period.

The segment date is expected to be displayed as

For approvals -'Min of Approved date'  to  'previous date of Denial'

For denials- 'Min of Denied date'  to  'previous date of Approval'

As an application id could be approved and denied several times, request your help in calculating the 'Segmented Interval' field for multiple approvals and denials respectively. how to identify whether the application was approved for the first time or second time to calculate respective interval

#1

Application ID	Approval Status	Start Date	End Date	Segmented Status	Segmented Date
1	Approved	5/21/2020	12/31/2020	Approved	5/21/2020	Approved for the first time
1	Approved	5/21/2020	12/31/2020	Approved	5/22/2020
1	Approved	5/21/2020	12/31/2020	Approved	5/23/2020
1	Approved	5/21/2020	12/31/2020	Approved	5/24/2020
1	Denied	5/21/2020	12/31/2020	Denied	8/6/2020	Denied for the first time
1	Approved	5/21/2020	12/31/2020	Approved	9/1/2020	Approved for the second time
1	Denied	5/21/2020	12/31/2020	Denied	10/11/2020	Denied for the second time

#2 Expected result

Application ID	Approval Status	Start Date	End Date	Segmented Status	Segmented Interval
1	Approved	5/21/2020	12/31/2020	Approved	5/21/2020 - 8/5/2020
1	Denied	5/21/2020	12/31/2020	Denied	8/6/2020 - 8/31/2020
1	Approved	5/21/2020	12/31/2020	Approved	9/1/2020 - 10/10/2020
1	Denied	5/21/2020	12/31/2020	Denied	10/11/2020 - 12/31/2020

[Saravanan_Desingh]

One solution is,

tab1:
LOAD *, If([Segmented Status]<>Peek('Segmented Status'),1) As K1;
LOAD RecNo() As ID,* INLINE [
    Application ID, Approval Status, Start Date, End Date, Segmented Status, Segmented Date
    1, Approved, 5/21/2020, 12/31/2020, Approved, 5/21/2020
    1, Approved, 5/21/2020, 12/31/2020, Approved, 5/22/2020
    1, Approved, 5/21/2020, 12/31/2020, Approved, 5/23/2020
    1, Approved, 5/21/2020, 12/31/2020, Approved, 5/24/2020
    1, Denied, 5/21/2020, 12/31/2020, Denied, 8/6/2020
    1, Approved, 5/21/2020, 12/31/2020, Approved, 9/1/2020
    1, Denied, 5/21/2020, 12/31/2020, Denied, 10/11/2020
];

tab2:
NoConcatenate
LOAD *, [Segmented Date]&' - '&Alt(Date(Peek('Segmented Date')-1),[End Date]) As [Segmented Interval]
Resident tab1
Where K1
Order By ID Desc;

Drop Table tab1;

[Saravanan_Desingh]

Output: