I need your help with a formula to put a limit in a field

[erivera10]

For example, if I have a field "natural" with value 12, the limit is 9, so in double filed  write 9, and the rest write in another field triple, with value 3.

hours:

LOAD * INLINE [

    employee, natural, double, triple

    1, 8, 8, 0

    2, 9, 9, 0

    3, 11, 9, 2

    4, 12, 9, 3

    5, 13, 9, 4

    6, 10, 9, 1

    7, 11, 9, 2

    8, 15, 9, 6

    9, 11, 9, 2

];

how can I do something like this example, since I only have the natural field?

[vishsaggi]

Try this?

hours:

LOAD employee,

     natural,

     IF(natural > 9, 9, natural) as double,

     IF(Len(natural) =1, 0, Left(natural,1) + Right(natural,1)) as triple

INLINE [

    employee, natural

    1, 8

    2, 9

    3, 11

    4, 12

    5, 13

    6, 10

    7, 11

    8, 15

    9, 11

];

[Anonymous · Accepted solution]

I haven't test it but how about:

Hours:

LOAD employee,

     natural,

   rangemin(natural,9) as double,

natural-rangemin(natural,9) as triple

INLINE [

    employee, natural

    1, 8

    2, 9

    3, 11

    4, 12

    5, 13

    6, 10

    7, 11

    8, 15

    9, 11

];

[erivera10]

Not works when natural field value more than 20.

employee	natural	double	triple
1	8	8	0
2	9	9	0
3	11	9	2
4	12	9	3
5	13	9	4
6	10	9	1
7	11	9	2
8	13	9	4
9	14	9	5
10	15	9	6
11	16	9	7
12	17	9	8
13	18	9	9
14	19	9	10
15	20	9	2
16	21	9	3

[vishsaggi]

What is your expected output for your data highlighted in yellow?

Ok got it. Did not realilze the triple field is natural - 9.

[erivera10]

It works, thank you.

LOAD employee,

          natural,

          rangemin(natural,9) as double,

          natural-rangemin(natural,9) as triple,

          rangemin(natural,9)+(natural-rangemin(natural,9)) as result

          INLINE [

          employee, natural

         1, 8

         2, 9

         3, 11

         4, 12

         5, 13

         6, 10

         7, 11

         8, 13

         9, 14

         10,15

         11,16

         12,17

         13,18

         14,19

         15,20

         16,21

         17,1009

         18,909

];

employee	natural	double	triple	result
1	8	8	0	8
2	9	9	0	9
3	11	9	2	11
4	12	9	3	12
5	13	9	4	13
6	10	9	1	10
7	11	9	2	11
8	13	9	4	13
9	14	9	5	14
10	15	9	6	15
11	16	9	7	16
12	17	9	8	17
13	18	9	9	18
14	19	9	10	19
15	20	9	11	20
16	21	9	12	21
17	1009	9	1000	1009
18	909	9	900	909

[erivera10]

If you sum the double and triple fields, the result should be the same as natural field.

For example:

Natural =11

double=9

triple=2

[MarcoWedel]

Hi,

one example adding preceding loads to this solution:

hours:
LOAD *, double+triple as result;
LOAD *, natural-double as triple;
LOAD *, RangeMin(natural,9) as double
Inline [
    employee, natural
    1, 8
    2, 9
    3, 11
    4, 12
    5, 13
    6, 10
    7, 11
    8, 15
    9, 11
];

hope this helps

regards

Marco