if value is between

[Anonymous]

Hi!

I need to create a connections between several tables. 

Most of them contains data about consumed quantities of products for different users. And one is Price table. But I cannot link the relationships as the price table does not hold unique values. It's because there are different products and the price is dependant on the amount! 
f.e. 
Resource ID                                                       Minimum Value   EUR

027f6f3a-0042-490f-90d3-9797c874de75	10000	5.430852
027f6f3a-0042-490f-90d3-9797c874de75	100000	5.042934
027f6f3a-0042-490f-90d3-9797c874de75	1000000	4.655016
ce59fb30-90f4-471b-969f-d22fb4725e98	10000	0.4848975
ce59fb30-90f4-471b-969f-d22fb4725e98	100000	0.387918
ce59fb30-90f4-471b-969f-d22fb4725e98	1000000	0.2909385
135366a9-a9a5-4cd3-bd32-85b7c2512ffd	10000	1.745631
135366a9-a9a5-4cd3-bd32-85b7c2512ffd	100000	1.551672
135366a9-a9a5-4cd3-bd32-85b7c2512ffd	1000000	1.357713
72fb1002-5869-4aa7-9c31-491d73709a19	9000	0.1551672
72fb1002-5869-4aa7-9c31-491d73709a19	99000	0.1163754
72fb1002-5869-4aa7-9c31-491d73709a19	999000	0.0775836
e0d5dad9-c5ce-433d-bea3-2b4c699c3d5e	99900	0.003801596
e0d5dad9-c5ce-433d-bea3-2b4c699c3d5e	499900	0.002637842
e0d5dad9-c5ce-433d-bea3-2b4c699c3d5e	2999900	0.002327508
e0d5dad9-c5ce-433d-bea3-2b4c699c3d5e	5999900	0.002094757
e0d5dad9-c5ce-433d-bea3-2b4c699c3d5e	9999900	0.00193959
04f2be54-5cfe-4ad7-97f3-0badfc1dc247	51	0.387918
ba2b4df6-e886-4cf2-9818-33f27d22b3cf	240000	0.0775836
ba2b4df6-e886-4cf2-9818-33f27d22b3cf	750000	0.0387918
ba2b4df6-e886-4cf2-9818-33f27d22b3cf	1000000	0.01551672
b2b47a8b-72e0-4dfd-8888-a5009214f668	1024	0.042127895

Can you please help me with the best and easy way? Should I remove all the duplicates from Price table, call it Clean Price Table and create Relationships between Cleaned Price table, Full price table ? Then I will be able to link the countries with Cleaned Price table too.  But how do I set that IF a customer has had used product e0d5dad9-c5ce-433d-bea3-2b4c699c3d5e with  value higher than 99900 and lower than 499900 - the price is returned 0.003801596 and it is returned FOR Country (linked with Clean Price), but takes value from Full Price Table.

[v-piga-msft]

Hi @Anonymous ,

I still have a little confused about your senario.

Do you want to create the relationship for your tables?

If it is, please share your data sample and your desired output so that I could have a test on it.

Best Regards,

Cherry

[Anonymous]

Hi, 

I created one more table with unique data in it. 

But I still cannot make the data "loop". 

For example I have some product (computer programme f.e.) and it has not been used 24/7 but only 8 hrs. And the payment is only for these 8 hours. 

And there is a pricing list that depends on amount of product consumed, f.e. it the product is consumed 0-5 then price is 0.003. If product is consumed 5-10, then price is 0.004. And it is in the table marked as Minimal value. 

What I need is to see the pricing client gets, if he has used certain amount of product.
https://www.dropbox.com/s/ifalsxwpqtn967i/sample.xlsx?dl=0 in this sample you can see that I have different products, but I cannot link the price to them, as there are different amounts of use. 
And I write a formula IF bigger than 0, "price", IF bigger 5, "price".. I think I will have to rewrite code in case when new prices / minimal values will come in! IS it possible to create some kind of indexing and then set the price? 

Will wait for your help. Best, Ani

[Anonymous · Accepted solution]

Can anyone please help me?