Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a corpus of products that have a production time assigned beforehand:
PRODUCTS |
TIME_PRODUCT |
product 1 |
00:09:00 |
product 2 | 00:09:30 |
product 3 | 00:12:00 |
product 4 | 00:13:30 |
So the product 1 takes 9 minutes to complete, product 2 9 minutes and 30 and so on. What I have, in another table, is the amount of time a user has taken to complete the same product:
USER | PRODUCTS | TIME_USED |
user 1 | product 1 | 00:09:00 |
user 1 | product 2 | 00:08:30 |
Now what I need to do is, couple these two together to obtain a formula and get a percentage. The final percentage would be how many times has the user gotten a timing equal or lower to the stated time?
I tried working on the following formula, which unfortunately is not apt for the Sum function:
Sum(
{$<Weekday={'Lu', 'Ma', 'Me', 'Gi', 'Ve'}>}
If([Time_Used] < [Time_Product], 1, 0)
) /
Count({$<Weekday={'Lu', 'Ma', 'Me', 'Gi', 'Ve'}>} [Time_Used]) * 100
I suggest to map TIME_PRODUCT to TIME_USED and calculating there the offset as well as an appropriate flag to avoid if-loops and probably some aggr() within the UI.
Thank you. By mapping the two columns, do you mean to have a flag that does that in the load script? Like Time_product - time_used and get the difference?
Yes. It looked that TIME_PRODUCT are planned values which could in this way directly loaded as mapping table and within the user-table they are called like:
load *, sign(Offset) as Flag;
load *, applymap('myMapping', PRODUCTS) - TIME_USED as Offset
from X;
Thank you, I tried to make this work, and I get the idea behind it. In the sign() function I enter the difference between the two numbers, and I get the mapping of products in the second row according to the offset.
However, the main problem for me is that I have time for the products in a different table than the one in which I have the time used.
The mapping combines both tables:
myMapping: mapping load PRODUCTS, TIME_PRODUCT from Y;
t: load *, sign(Offset) as Flag;
load *, applymap('myMapping', PRODUCTS) - TIME_USED as Offset
from X;
Hi @marcus_sommer I tried to make this work, and I got to linking the two tables together. However, no luck with getting the mapping and offset.
Here is a breakdown of the load editor script:
ProductStationTimes:
LOAD
Product,
Interval(Num#(Time/1440),'hh:mm:ss') as Time_Product
INLINE [
Cella, Product, Time
CELL21, Product1, 9,00
CELL21, Product1, 9,00
];
And the main table:
USER | PRODUCTS | TIME_USED |
user 1 | product 1 | 00:09:00 |
user 1 | product 2 | 00:08:30 |
These two are linked by the product. Here is the mapping I used in the load editor script:
myMapping:
MAPPING LOAD Product, Time_Product
Resident ProductStationTimes;
t: LOAD *, SIGN(Offset) as Flag;
LOAD *, APPLYMAP('myMapping', Time_Product) - Duration as Offset
Resident MainTable;
When I try to run this script, I get an error "Field (Time_Product) not found" in the row with the 3rd load, second to last one. Do you have any idea what could cause this? Any help is very appreciated, thank you!
The logic of a mapping is:
m: mapping load [LOOKUP-Value], [RETURN-Value] from ...;
and by calling it per applymap() it's:
applymap('m', [LOOKUP-Value], 'no Match') as ...;
which means in your case you need to change the field in the applymap(), like:
APPLYMAP('myMapping', Product) - Duration as Offset