Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to get count of duplicate values under 90 days

Hi,

In excel i have the following table:

case_idrepair_nrissue_dateend_date
1A11-1-20143-2-2014
2A22-2-20143-2-2014
3A33-2-20143-2-2014
1A420-2-20141-3-2014
1A51-11-201410-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:

qlik1.png

Can anyone help me out how this will be possible?

17 Replies
Not applicable
Author

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)

Not applicable
Author

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?

Not applicable
Author

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)

Not applicable
Author

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):

qlik1.png

Not applicable
Author

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

Not applicable
Author

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???

Not applicable
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob

Not applicable
Author

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