Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
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?
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)
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):
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
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???
I dont understand the problem here, Please post sample data & qvw so I will help you.
=count({<case_id={"=end_date-issue_date < 90"}>}DISTINCT case_id)
-Rob
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