Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
erivera10
Creator
Creator

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
Anonymous
Not applicable

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

];

View solution in original post

7 Replies
vishsaggi
Champion III
Champion III

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
Not applicable

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
Creator
Creator
Author

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
Champion III
Champion III

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

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

erivera10
Creator
Creator
Author

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
Creator
Creator
Author

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