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]

Hi , You need to create the Flag field in the script and use this Flag on the set analysis.

Temp:

LOAD

    case_id,repair_nr,issue_date,end_date

FROM Excel_file.xls;

Final:

LOAD

     *,

     IF(Previous(case_id)=case_id and end_date - today() <= 90 ,1 , 0) AS FLAG

Resident Temp;

Drop Table Temp;

Use Set analysis Expression to count the case_id's:

     Count(DISTINCT {$<FLAG={1}>} case_id)

[#3]

But what if you have for example case_id = 1A and then case_id = 1B and then again case_id = 1A then the previouse(case_id)= case_id wont work right?

[#4]

Sorry, I forgot Order By clause in my last post.

Temp:

LOAD

    case_id,repair_nr,issue_date,end_date

FROM Excel_file.xls;

Final:

LOAD

     *,

     IF(Previous(case_id)=case_id and end_date - today() <= 90 ,1 , 0) AS FLAG

Resident Temp Order by case_id, repair_nr;

Drop Table Temp;

Use Set analysis Expression to count the case_id's:

     Count(DISTINCT {$<FLAG={1}>} case_id)

[#5]

ok this works now, and will it also be possible to not count if repair_case begins with for example NL or CZ

So you get this(here it shows all the duplicates but only counts duplicate serials which doesnt start with NL):

[#6]

Add one more validation while FLAG creation like below:

IF(Previous(case_id)=case_id and end_date - today() <= 90 ,IF(WildMatch(repair_nr, 'NL*','CZ*'),0,1) , 0) AS FLAG

[#7]

what if i have

Final:

LOAD DISTINCT

but the end_date is in another table then temp for example you have also table temp2 where the end_dates are.. temp table and temp2 table is connected with the field id. JOIN and DISTINCT will sometimes bring problems..

How to do it then???

[#8]

I dont understand the problem here, Please post sample data & qvw so I will help you.

[rwunderlich]

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

-Rob

[#10]

what if you have this:

Temp:

LOAD

    case_id,repair_nr

FROM Excel_file.xls;

Temp2:

LOAD

    case_id,issue_date,end_date

FROM Excel_file.xls;

in the Final: how can i get the issue and end dates to do the calculation