Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
madhudinesh
Partner
Partner

Need help

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 IDApproval StatusStart DateEnd DateSegmented StatusSegmented Date 
1Approved5/21/202012/31/2020Approved5/21/2020Approved for the first time
1Approved5/21/202012/31/2020Approved5/22/2020
1Approved5/21/202012/31/2020Approved5/23/2020
1Approved5/21/202012/31/2020Approved5/24/2020
1Denied5/21/202012/31/2020Denied8/6/2020Denied for the first time
1Approved5/21/202012/31/2020Approved9/1/2020Approved for the second time
1Denied5/21/202012/31/2020Denied10/11/2020Denied for the second time

 

#2 Expected result

Application IDApproval StatusStart DateEnd DateSegmented StatusSegmented Interval
1Approved5/21/202012/31/2020Approved5/21/2020 - 8/5/2020
1Denied5/21/202012/31/2020Denied8/6/2020 - 8/31/2020
1Approved5/21/202012/31/2020Approved9/1/2020 - 10/10/2020
1Denied5/21/202012/31/2020Denied10/11/2020 - 12/31/2020
2 Replies
saran7de
Master
Master

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;
saran7de
Master
Master

Output:

commQV89.PNG