topic Select value over multiple nested conditions in ODS and Base Reporting

Select value over multiple nested conditions

vivudao — Wed, 26 Aug 2015 18:19:54 GMT

How can i select a correct Group_ID who has to be satisfied following conditions:

1a) If # of visits = 1 then select "TIE" Group_ID with most recent Max_date

1b) else If # of visits = 1 AND Max_date is the same date then select "TIE" Group_ID with greatest Cost

1c) else If # of visits = 1 AND most recent Max_date is the same date AND Cost is the same then leave blank

2a) else if # of visits > 1 then select "TIE" Group_ID with greatest time

2b) else If # of visits > 1 AND Time is equal then select "TIE" Group_ID with Max_date .

2c) else If # of visits > 1 AND time is equal AND the Max_date is the same then select "TIE" Group_ID with greatest Cost.

2d) else If # of visits > 1 AND length of time between first and last E&M visits is equal AND the Max_date is the same AND the Cost are the same then leave blank.

My data is below:

ID	Period	Group_ID	EM counts	Min_date	Max_date	Cost	time
AAA	1	4Q4936	1	2/3/2015	2/4/2015	0	0
AAA	1	66532	1	24/9/2014	24/9/2015	0	0
AAA	2	66532	2	22/7/2013	7/3/2014	$228	228
AAA	2	454936	1	28/2/2014	28/2/2014	0	0
AAA	2	4Q4936	1	8/1/2013	8/2/2013	0	1
AAA	3	1829	26	5/5/2014	28/4/2015	0	358
AAA	3	1825	26	15/5/2014	28/4/2015	358	348.00

Re: Select value over multiple nested conditions

ballardw — Wed, 26 Aug 2015 19:38:35 GMT

First thing: Define "visit"

Second: how do we identify " "TIE" Group_ID"

Third: "Max_date is the same" same as ? Max_date or Min_date

Fourth: "AND Time is equal" equal to what, assumes you mean value to time but which ones may not be obvious

For you example data, what would the output look like?

If you don't have an example of each case of your conditions it can be hard to test code.

Re: Select value over multiple nested conditions

vivudao — Wed, 26 Aug 2015 20:18:21 GMT

First thing: Define "visit" ----Visit is EM count

Second: how do we identify " "TIE" Group_ID" ---TIE is having same EM counts, or Max_date, or Cost or Time

Third: "Max_date is the same" same as ? Max_date or Min_date-----Max_date is the same as Max_date

Fourth: "AND Time is equal" equal to what, assumes you mean value to time but which ones may not be obvious ---Time is calculated by the Max_date substract Min_date

My output is to find the winner group ID for each period. for example, in period 1 both group IDs had same EM counts of 1, then follow rule 1a and 1b the second one has most recent Max_date, so the Group ID= 66532 is the winner for period 1, and so on...

Please let me know if it's still not clear.

Thanks,