Measure to calculate how many times has the duration corresponded to the theoretical time

[alespooletto]

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

[marcus_sommer]

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.

[alespooletto]

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? 

[marcus_sommer]

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;

[alespooletto]

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.

[marcus_sommer]

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;

[alespooletto]

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!

[marcus_sommer]

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