topic Re: To pick required Value based on multiple conditions in App Development

To pick required Value based on multiple conditions

hitha1512 — Mon, 20 Dec 2021 21:08:46 GMT

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

Re: To pick required Value based on multiple conditions

sunny_talwar — Wed, 01 Jul 2020 12:14:24 GMT

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

Re: To pick required Value based on multiple conditions

hitha1512 — Wed, 01 Jul 2020 12:18:10 GMT

In script:)

Re: To pick required Value based on multiple conditions

sunny_talwar — Wed, 01 Jul 2020 12:40:11 GMT

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.

Re: To pick required Value based on multiple conditions

Saravanan_Desingh — Thu, 02 Jul 2020 03:52:34 GMT

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;

Re: To pick required Value based on multiple conditions

Saravanan_Desingh — Thu, 02 Jul 2020 03:53:26 GMT

Output:

Re: To pick required Value based on multiple conditions

Deepak87 — Thu, 02 Jul 2020 06:29:34 GMT

Hi Sunny,

@sunny_talwar

is there a way to do it in front end?

Regards,

Deepak K M

Re: To pick required Value based on multiple conditions

hitha1512 — Thu, 02 Jul 2020 08:52:17 GMT

@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

Re: To pick required Value based on multiple conditions

sunny_talwar — Thu, 02 Jul 2020 11:51:37 GMT

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?

Re: To pick required Value based on multiple conditions

sunny_talwar — Thu, 02 Jul 2020 12:14:09 GMT

Yes this is doable on the front end as well

Re: To pick required Value based on multiple conditions

Deepak87 — Thu, 02 Jul 2020 12:20:10 GMT

Hi @sunny_talwar ,

How to do it in front end?

also the tier value are 5 digit now. but if the tier value field length is not constant means, how to do it?

Regards,

Deepak

Re: To pick required Value based on multiple conditions

sunny_talwar — Thu, 02 Jul 2020 12:36:16 GMT

You can try this using Rank() function on top of -(Rank + [Tier Value]/1e5 + Date/1e10) to determine rank 1... Rank 1 will be your lowest value.

Also, I would need to see an example for what you mean by Tier Value. This is kind of dependent on each value for each section because I am trying to weight them a little differently. But based on possible range of values, we can determine the weights.

Re: To pick required Value based on multiple conditions

Deepak87 — Thu, 02 Jul 2020 13:56:12 GMT

Hi @sunny_talwar

Tier Value is an amount. can range from 0 to max a million

Re: To pick required Value based on multiple conditions

sunny_talwar — Thu, 02 Jul 2020 15:00:21 GMT

Sorry, but are you working on a similar issue or do you work with @hitha1512 ? I just want to make sure I understand how your issue is related to the one in question.

In that case, may be create value like this

Rank*1e10 + [Tier Value]/1e5 + Date/1e10

Multiplied Rank by 10,000,000,000 to make sure Tier Value doesn't end up exceeding Rank (hence adding more weight to Rank).

Do you get the idea? It is all about giving more weight to the field which is first in order of decision making and less weight to the field which is last in order of decision making.

Re: To pick required Value based on multiple conditions

Deepak87 — Fri, 03 Jul 2020 13:45:55 GMT

Hi @sunny_talwar ,

Kind of me and @hitha1512 are working on same issue.

I tried your solution and it's seems to be working.

Thanks a lot.

Re: To pick required Value based on multiple conditions

sunny_talwar — Sun, 05 Jul 2020 23:01:35 GMT

Okay that is great. @hitha1512 is your issue resolved as well? If it is, then please close this thread.

Re: To pick required Value based on multiple conditions

hitha1512 — Tue, 07 Jul 2020 18:09:16 GMT

@sunny_talwar me and Deepak were working on the same issue.

thanks a ton 🙂 much appreciated 🙂