Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
erivera10
Contributor

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

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?

1 Solution

Accepted Solutions
atkinsow
Valued Contributor II

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

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

];

7 Replies
vishsaggi
Esteemed Contributor III

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

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

];

atkinsow
Valued Contributor II

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

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
Contributor

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

Not works when natural field value more than 20.

employee natural double triple
1880
2990
31192
41293
51394
61091
71192
81394
91495
101596
111697
121798
131899
1419910
152092
162193
vishsaggi
Esteemed Contributor III

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

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

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

erivera10
Contributor

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

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
18808
29909
3119211
4129312
5139413
6109110
7119211
8139413
9149514
10159615
11169716
12179817
13189918
141991019
152091120
162191221
171009910001009
189099900909
erivera10
Contributor

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

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

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

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