topic Re: Rank Issue in Qlikview in QlikView

Rank Issue in Qlikview

Sat, 18 Mar 2017 05:42:34 GMT

Dear Experts,

I am stuck with a small issue and I will appreciate if you can help me resolve the same

We have a data set which contains multiple version of record. We have a requirement to select latest version of the case from a given date rage

Main table and the event table has 1 to many relationship i.e one case can have multiple events. The tables are joined together by AER_ID

Main

CASE_NO	VERSION_NO	COUNTRY	SERIOUSNESS	LATEST_RECV_DATE	AER_ID
ABC	0	USA	S	1/1/2015	1
ABC	1	CANADA	S	1/1/2016	2
ABC	2	USA	NS	1/1/2017	3
XYZ	0	USA	S	1/1/2015	4
XYZ	1	ITALY	S	1/1/2016	5
MNO	0	USA	S	1/1/2015	6
MNO	1	USA	NS	1/1/2016	7
MNO	2	USA	S	1/1/2017	8
HIJ	0	USA	S	1/1/2017	9
YYY	1	INDIA	NS	1/2/2012	10
YYY	2	MEX	NS	1/2/2013	11
TBD	3	YUN	NS	1/5/2014	12
OOO	0	ARG	S	1/1/2011	13
OOO	1	INDIA	NS	1/5/2011	14

Event Table

AER_ID	SEQ_REACT	PT_NAME	CASE_EVENT_ID
1	1	PT1	1
1	2	PT2	2
2	1	PT1	4
2	2	PT2	5
2	3	PT3	6
3	1	PT1	7
3	2	PT2	8
3	3	PT3	9
3	4	PT4	10
4	1	PT1	11
5	1	PT2	12
6	1	PT1	13
7	1	PT1	14
8	1	PT2	15
9	1	PT2	16
10	1	PT2	17
10	2	PT5	20
11	1	PT3	18
12	1	PT1	19
12	1	PT4	21

Our requirement is that based on the start date and end date from latest received date in the main table, the latest version of the record (based on version no within a case_no) should be selected

Now we are able to meet the requirement by using the RANK function in the main table

V_Rank =

aggr( Rank(sum({1<LATEST_RECV_DATE={">=$(=Date($(vStartDate),'MM/DD/YYYY')) <=$(=Date($(vEndDate),'MM/DD/YYYY'))"}>}VERSION_NO),4),CASE_NO, VERSION_NO)

Expression to limit the records =Count( if( $(V_Rank) =1 , AER_ID) )

Unfortunately the same expression is not working the Event table. I have tried the following two expression and they are not producing the right results

#Events -1

Count( if( $(V_Rank) =1 , CASE_EVENT_ID) )

#Events -2

=Sum(aggr(If( Rank(sum({1<LATEST_RECV_DATE={">=$(=Date($(vStartDate),'MM/DD/YYYY')) <=$(=Date($(vEndDate),'MM/DD/YYYY'))"}>}VERSION_NO),4)<= 1, count(CASE_EVENT_ID)) ,CASE_NO, VERSION_NO) )

CASE_NO	COUNTRY	SERIOUSNESS	VERSION_NO	LATEST_RECV_DATE	WORKING	#Events - 1	#Events -2
ABC	USA	NS	2	1/1/2017	1	1	4
HIJ	USA	S	0	1/1/2017	1	1	1
MNO	USA	S	2	1/1/2017	1	1	1
TBD	YUN	NS	3	1/5/2014	1	1	2
XYZ	ITALY	S	1	1/1/2016	1	1	1
YYY	MEX	NS	2	1/2/2013	1	1	1

While expression Event -1 is returning the results which is same as the count of cases, #Event – 2 returns the results even for the cases which should not be selected

e.g. for the start date and end date 1/1/2011, I should have only 1 case and 0 events

i.e if # of cases is 0 then # events should also be 0

CASE_NO	COUNTRY	SERIOUSNESS	VERSION_NO	LATEST_RECV_DATE	WORKING	#Cases	#Events - 1	#Events -2
ABC	USA	S	0	1/1/2015	0	0	2
HIJ	USA	S	0	1/1/2017	0	0	1
MNO	USA	S	0	1/1/2015	0	0	1
OOO	ARG	S	0	1/1/2011	1	1	0	0
TBD	YUN	NS	3	1/5/2014	0	0	2
XYZ	USA	S	0	1/1/2015	0	0	1
YYY	INDIA	NS	1	1/2/2012	0	0	2

I will really appreciate if you can help me with the same. The sample QVW file is attached

Re: Rank Issue in Qlikview

sunny_talwar — Sat, 18 Mar 2017 16:02:33 GMT

What is the output that you expect to get?

Re: Rank Issue in Qlikview

Sun, 19 Mar 2017 21:39:23 GMT

Thankyou Sunny for the prompt response

Scenario 1 :The Expected output for start date = 1/1/2011 and the end date 1/1/2011 should be

CASE_NO	COUNTRY	SERIOUSNESS	VERSION_NO	LATEST_RECV_DATE	WORKING	#Cases	#Events - 1	#Events -2
OOO	ARG	S	0	1/1/2011	1	1	0	0

i.e. Total # of Cases should be 1 and # No of Events should be 0

Scenario 2 :The Expected output for start date = 1/1/2011 and the end date 1/1/2015 should be

CASE_NO	COUNTRY	SERIOUSNESS	VERSION_NO	LATEST_RECV_DATE	WORKING	#Cases	#Events - 1	#Events -2
ABC	USA	S	0	1/1/2015	1	1	1	2
MNO	USA	S	0	1/1/2015	1	1	1	1
OOO	INDIA	NS	1	1/5/2011	1	1	0	0
TBD	YUN	NS	3	1/5/2014	1	1	1	2
XYZ	USA	S	0	1/1/2015	1	1	1	1
YYY	MEX	NS	2	1/2/2013	1	1	1	1

i.e. Total # of Cases should be 6 and # No of Events should be 7

Some additional info

I have a datamart containing SCD2 records base of CASE dimension i.e there are multiple version of cases within the system.

There are attributes related to case ( in our scenario it is Events . a case can have 0 or many events)

Dashboard should load all the records from the database. Based on user's selection of start and end date, the latest version of the case and the corresponding events should be selected.

What I am able to achieve so far is the selection of the proper cases based on user selection but not the underlying events

Re: Rank Issue in Qlikview

Mon, 20 Mar 2017 20:16:55 GMT

Found the answer. Thankyou guys for the help

Re: Rank Issue in Qlikview

Thu, 23 Mar 2017 23:53:22 GMT

Hi Guys,

Can someone, please provide an alternate solution. The solution that I implemented involve modifying expression in all the charts and it is not working in all the charts especially if the dimension is coming from the Event table

is there a way to apply a global filter on all the dashboard tabs such as (V_Rank = 1)

so that all the attributes corresponding to latest version of the records are selected

Re: Rank Issue in Qlikview

sunny_talwar — Fri, 24 Mar 2017 09:11:46 GMT

What was your current approach to do this? Can you share that?