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

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

erivera10 — Wed, 25 Nov 2020 16:16:04 GMT

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?

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

vishsaggi — Wed, 25 Oct 2017 18:17:28 GMT

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

];

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

Anonymous — Wed, 25 Oct 2017 18:36:16 GMT

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

];

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

erivera10 — Wed, 25 Oct 2017 18:40:18 GMT

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

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

vishsaggi — Wed, 25 Oct 2017 18:44:48 GMT

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

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

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

erivera10 — Wed, 25 Oct 2017 18:49:55 GMT

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

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

erivera10 — Wed, 25 Oct 2017 18:57:23 GMT

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

MarcoWedel — Wed, 25 Oct 2017 21:47:49 GMT

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

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

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

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