how to get count of duplicate values under 90 days

[#1]

Hi,

In excel i have the following table:

case_id	repair_nr	issue_date	end_date
1A	1	1-1-2014	3-2-2014
2A	2	2-2-2014	3-2-2014
3A	3	3-2-2014	3-2-2014
1A	4	20-2-2014	1-3-2014
1A	5	1-11-2014	10-11-2014

For every same case_id i want to check if the end_date from one case_id and the issue_date from another case id falls under 90 days, if yes then there must come a column for count where this count will be +1, for example:

In this table there are three same case_ids => 1A. In qlikview i first need to check if there are matching case_ids in a table, if so then to check if the end_date from for example 1A with repair nr 1 and the issue_date from 1A with repair_nr 4 and repair_nr 5 etc is under 90 days. also if end date from 1A with repair_nr 4 and issue date from 1A with repair nr 5 is also below 90 days etc.

So for example here there are 2 matching 1A case ids that are under 90 days so count will be 1 for these.

So if case_id is the same then check end date from that case with all other issue date if it falls under 90 days if yes then get a count table and count the case_ids..

so for example(see serial as case_id) and for example every repair_nr with other letters other than starting with BB must be removed from the list so outcome with count will be like this:

Can anyone help me out how this will be possible?

[#2]

Do in need to insert this as an expression or at script side? and what if issue date and end date come are in another table although the table are connected to each other?

[rwunderlich]

Use as an expression. It doesn't matter if the fields are in different tables, as long as they are associated.

Rob

[#4]

Does not work. But if you use a distinct how will it be possible to check count the double case_id

for example case_id = 1A + repair_nr = 1 and case_id = 1A + repair_nr 2 + case_id 1A + repair_no=3 must give a count of 2(not 1 nor 3)

[rwunderlich]

You can add in the repair_nr if you want to consider that also:

=count({<case_id={"=end_date-issue_date < 90"}>}DISTINCT case_id & repair_nr)

But that's different than your original question.

"So for example here there are 2 matching 1A case ids that are under 90 days so count will be 1 for these."

-Rob

[#6]

Dear Rob,

I will try to explain more clearly.

Imagine i have this table

Case_id:          repair_nr         issue_date               end_date
1A                    BG1234           1-2-2014                2-2-2014

1A                    BG2234          5-2-2014                 12-2-2014

1A                    RO3234          10-2-2014               12-2-2014

repair_nr will always be unique. Imagine case_id=1A will be brought for repair 2 times. 1 time on 1-2-2014 and 1 time on 5-2-2014, on 10-2-2014 the repair with BG2234 moved to another repair center.

So in this scenario for all same case_ids we want to calculate the second time returns. So it must be 1 time and not 2.

Because the RO3234 is not a second time return but its just a tranfser from bg to ro.

Will this be possible?

[rwunderlich]

So having the same end_date makes it the same repair? In that case include end_date

=count({<case_id={"=end_date-issue_date < 90"}>}DISTINCT case_id & end_date)

It gets a little more complicated if duration for both repairs should be considered as 12-2-2014 minus 502-2014.

-Rob

[#8]

No, a repair can for example be transeffered to another repair center and then it will get another repair_nr for example then it will start with RO,

So every case with repair nr RO needs to be deducted..

[#9]

=count({<case_id={"=end_date-issue_date < 90"}>}DISTINCT case_id & end_date)

This doesnt work it doesnt give any results... i have qlikview 10