cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anni Frequent Visitor
Frequent Visitor

if value is between

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-9797c874de75100005.430852
027f6f3a-0042-490f-90d3-9797c874de751000005.042934
027f6f3a-0042-490f-90d3-9797c874de7510000004.655016
ce59fb30-90f4-471b-969f-d22fb4725e98100000.4848975
ce59fb30-90f4-471b-969f-d22fb4725e981000000.387918
ce59fb30-90f4-471b-969f-d22fb4725e9810000000.2909385
135366a9-a9a5-4cd3-bd32-85b7c2512ffd100001.745631
135366a9-a9a5-4cd3-bd32-85b7c2512ffd1000001.551672
135366a9-a9a5-4cd3-bd32-85b7c2512ffd10000001.357713
72fb1002-5869-4aa7-9c31-491d73709a1990000.1551672
72fb1002-5869-4aa7-9c31-491d73709a19990000.1163754
72fb1002-5869-4aa7-9c31-491d73709a199990000.0775836
e0d5dad9-c5ce-433d-bea3-2b4c699c3d5e999000.003801596
e0d5dad9-c5ce-433d-bea3-2b4c699c3d5e4999000.002637842
e0d5dad9-c5ce-433d-bea3-2b4c699c3d5e29999000.002327508
e0d5dad9-c5ce-433d-bea3-2b4c699c3d5e59999000.002094757
e0d5dad9-c5ce-433d-bea3-2b4c699c3d5e99999000.00193959
04f2be54-5cfe-4ad7-97f3-0badfc1dc247510.387918
ba2b4df6-e886-4cf2-9818-33f27d22b3cf2400000.0775836
ba2b4df6-e886-4cf2-9818-33f27d22b3cf7500000.0387918
ba2b4df6-e886-4cf2-9818-33f27d22b3cf10000000.01551672
b2b47a8b-72e0-4dfd-8888-a5009214f66810240.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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
anni Frequent Visitor
Frequent Visitor

Re: if value is between

Can anyone please help me?

3 REPLIES 3
Community Support Team
Community Support Team

Re: if value is between

Hi @anni ,

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
anni Frequent Visitor
Frequent Visitor

Re: if value is between

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

 

 

Highlighted
anni Frequent Visitor
Frequent Visitor

Re: if value is between

Can anyone please help me?