To pick required Value based on multiple conditions

[hitha1512]

Hi Team,

Below is sample data I have and I need to calculate the best value based on few condition as below.

1. Check the Minimum rank, if there are more than one row\records, then

2. Check the minimum Tier value, if there are more than one row\records, then

3. Check the minimum date and consider that as Best Value

But Best value should be based on above condition and precedence only, First it has to check the rank, then tier value and then date.

Contract	Rank	Tier Value	Date
Premier Inc	9	42805	8/05/2019
MedAssets	9	30000	3/24/2019
Vizient	9	61150	8/05/2019
Cardinal Health	8	42805	8/23/2019
HealthTrust Purchasing Group	8	42805	8/05/2019

Result :

Contract	Rank	Tier Value	Date	Best Value
Premier Inc	9	42805	8/05/2019
MedAssets	9	30000	3/24/2019
Vizient	9	61150	8/05/2019
Cardinal Health	8	42805	8/23/2019
HealthTrust Purchasing Group	8	42805	8/05/2019	TRUE

Any help is appreciated!

Regards,

Hitha Dhani

[sunny_talwar]

You need this to be done in the script or a front end chart?

[hitha1512]

In script:)

[sunny_talwar]

May be try something like this

Table:
LOAD *,
	 Rank + [Tier Value]/1e5 + Date/1e10 as Temp;
LOAD * INLINE [
    Contract, Rank, Tier Value, Date
    Premier Inc, 9, 42805, 8/05/2019
    MedAssets , 9, 30000, 3/24/2019
    Vizient , 9, 61150, 8/05/2019
    Cardinal Health, 8, 42805, 8/23/2019
    HealthTrust Purchasing Group, 8, 42805, 8/05/2019
];

Left Join (Table)
LOAD Min(Temp) as Temp,
	 'TRUE' as [Best Value]
Resident Table;

DROP Field Temp;

I created a temp field which just combines the three fields which you use for your rating. I gave Rank the biggest weight and then Tier Value and finally the date field.

[Saravanan_Desingh]

One more solution in Script.

tab1:
LOAD * INLINE [
    Contract, Rank, Tier Value, Date
    Premier Inc, 9, 42805, 8/05/2019
    MedAssets , 9, 30000, 3/24/2019
    Vizient , 9, 61150, 8/05/2019
    Cardinal Health, 8, 42805, 8/23/2019
    HealthTrust Purchasing Group, 8, 42805, 8/05/2019
];

tab2:
NoConcatenate
LOAD * Resident tab1;

Right Join(tab2)
LOAD Min(Rank) As Rank Resident tab2;

Right Join(tab2)
LOAD Min([Tier Value]) As [Tier Value] Resident tab2;

Right Join(tab2)
LOAD Min(Date) As Date, 'TRUE' As [Best Value] Resident tab2;

Left Join(tab1)
LOAD * Resident tab2;

Drop Table tab2;

[Saravanan_Desingh]

Output:

[Deepak87]

Hi Sunny,

@sunny_talwar 

is there a way to do it in front end?

Regards,

Deepak K M

[hitha1512]

@sunny_talwar  @Saravanan_Desingh  Thank you. Both the solution work only if the first two conditions output is more than 1 row.

1. Check the Minimum rank, if there are more than one row\records, then

2. Check the minimum Tier value, if there are more than one row\records, then

3. Check the minimum date and consider that as Best Value.

in case the first check for minimum rank holds just one row.... best value should be populated at this level and the next 2 checks should be ignored.

similarly if second check gives one row as out put. last check should be ignored. 

Example :

Raw data :  

Contract	Rank	Tier Value	Date
Premier Inc	9	42805	8/05/2019
MedAssets	9	30000	3/24/2019
Vizient	9	61150	8/05/2019
Cardinal Health	8	42805	8/23/2019

here first check is on minimum rank....which is 8... since its output is just one row, best value will be populated for this row.

Contract	Rank	Tier Value	Date
Premier Inc	9	42805	8/05/2019
MedAssets	9	30000	3/24/2019
Vizient	9	61150	8/05/2019

here, first check holds 3 records for rank 9, hence this will go to next check for tier value. since the minimum value here is 3000, best value will be populated against this row. 

Any help is appreciated! 🙂

Regards,

Hitha Dhani

[sunny_talwar]

When I use this data

Contract	Rank	Tier Value	Date
Premier Inc	9	42805	8/05/2019
MedAssets	9	30000	3/24/2019
Vizient	9	61150	8/05/2019
Cardinal Health	8	42805	8/23/2019

and run this script

Table:
LOAD *,
	 Rank + [Tier Value]/1e5 + Date/1e10 as Temp;
LOAD * INLINE [
    Contract, Rank, Tier Value, Date
    Premier Inc, 9, 42805, 8/05/2019
    MedAssets , 9, 30000, 3/24/2019
    Vizient , 9, 61150, 8/05/2019
    Cardinal Health, 8, 42805, 8/23/2019
];

Left Join (Table)
LOAD Min(Temp) as Temp,
	 'TRUE' as [Best Value]
Resident Table;

DROP Field Temp;

I get this

When I use this data

Contract	Rank	Tier Value	Date
Premier Inc	9	42805	8/05/2019
MedAssets	9	30000	3/24/2019
Vizient	9	61150	8/05/2019

and I use this script

Table:
LOAD *,
	 Rank + [Tier Value]/1e5 + Date/1e10 as Temp;
LOAD * INLINE [
    Contract, Rank, Tier Value, Date
    Premier Inc, 9, 42805, 8/05/2019
    MedAssets , 9, 30000, 3/24/2019
    Vizient , 9, 61150, 8/05/2019
];

Left Join (Table)
LOAD Min(Temp) as Temp,
	 'TRUE' as [Best Value]
Resident Table;

DROP Field Temp;

I get this

All I have changed is the raw data and it looks the true shows up for the same row where you want it to be, right? Not sure if I am missing something?

[sunny_talwar]

Yes this is doable on the front end as well