17 Replies Latest reply: Sep 1, 2014 7:01 AM by Nunu Banpora RSS

    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?