count "broken contacts" as 1

[Qliksense_77]

Hi

I have data like below. The problem is that a lot of "open contacts" where ended and then started again on the 25-03-2022. I need to find a way where I can count those contacts as 1 contact. 

For instans row 2 and 3 in the sample below. I need to count that as 1 contact. The rows that has to be count as 1 can be identified by:

- they have the same "Id-person"

- one row is ended on the 25-03-2022

- One row is started on the 25-03-2022

It is not possible to just count distinct "Id-person", since the same "Id-person" can have many contacts(rows). 

In the sample below the count of contacts should be 4. 

The solution can both be in the script or a set expression for chart.

Anybody that can help me ?

Row(no)	ID-person	Id-contact	Date_start	Date_end	Number of contacts	Days
1	1	999736	28-09-2020	17-11-2020	1,0	50	1
2	1	846611	24-07-2020	25-03-2022	1,0	609	2
3	1	188672	25-03-2022	15-11-2022	1,0	235
4	2	1683438	09-07-2021	25-03-2022	1,0	259	3
5	2	82924	25-03-2022	15-11-2022	1,0	235
6	3	1656267	28-06-2021	25-03-2022	1,0	270	4
7	3	9553	25-03-2022	16-05-2022	1,0	52

[chaorenzhu · Accepted solution]

You can use previous() to compare current row "Date_start" with previous row "Date_end", provided that "Row(no)" is sorted. If not you need to use crosstab() to transform "Date_start" and "Date_end" into one date column and sort by both ID-person and date

sample:
load "Row(no)", "ID-person", "Id-contact", date#(Date_start) as Date_start, date#(Date_end) as Date_end
inline [
Row(no), ID-person, Id-contact, Date_start, Date_end
1, 1, 999736, 28-09-2020, 17-11-2020
2, 1, 846611, 24-07-2020, 25-03-2022
3, 1, 188672, 25-03-2022, 15-11-2022
4, 2, 1683438, 09-07-2021, 25-03-2022
5, 2, 82924, 25-03-2022, 15-11-2022
6, 3, 1656267, 28-06-2021, 25-03-2022
7, 3, 9553, 25-03-2022, 16-05-2022];

data:
load *, if(Date_start='25-03-2022' and previous(Date_end)='25-03-2022' and "ID-person"=previous("ID-person"),1,0) as broken_contact
resident sample
order by "Row(no)";

Now that you have created a flag caled broken_contact, simply use count({<broken_contact={0}>} "ID-person") to count "broken contacts"

[MayilVahanan]

Hi

Small modification in @chaorenzhu solution, it will work for all date of such scenario

data:
load *, if(Date_start=previous(Date_end) and "ID-person"=previous("ID-person"),1,0) as broken_contact
resident sample
order by "Row(no)";

DROP Table sample;

[Qliksense_77]

hi @MayilVahanan and @chaorenzhu 

Thank you ...It works 😀