topic Sum and Division in Load/Select without Grouping ? in QlikView

Sum and Division in Load/Select without Grouping ?

Fri, 23 May 2014 20:02:49 GMT

Hi All,

I am new to qlikview.

I have below question.

While We Load/Select Data in Load Statement,

I would also like to do Sum of two fields which are Numeric and I was to divide that Sum By 2.

How can we do that in Qlikview ?

Example :

Load

((A+B)/2) AS C

Resident D;

Any help would be Appreciated.

Re: Sum and Division in Load/Select without Grouping ?

maxgro — Fri, 23 May 2014 20:19:25 GMT

yes you can sum, divide, etc at the row level (or using info from different row with interrecord function, see online help)

load * inline [

a,b

1,2

3,4

5,6

];

t2:

load

(a+b)/2 as c,

(a+b) * previous(a) as d

Resident

Re: Sum and Division in Load/Select without Grouping ?

its_anandrjs — Fri, 23 May 2014 20:23:54 GMT

Yes you can do the same by using any resident table load

See the Example

tmpTable:

Load * inline

[

A,B

2,3

4,6

6,3

4,6

];

Calculations:

Load

((A+B)/2) AS C

Resident tmpTable;

Re: Sum and Division in Load/Select without Grouping ?

Sat, 24 May 2014 02:23:04 GMT

Anand,

can you please help with below ?

have a questions - how can we append leading zero in front of numbers and make it in 10 digit ?

Example :

We have : 12345

how can we make it - 0000012345 ?

Appreciate your help.

Re: Sum and Division in Load/Select without Grouping ?

its_anandrjs — Sat, 24 May 2014 05:14:35 GMT

Add new field in the load script see the example

LOAD *,Num(Val,'0000000000') as NewVal;

LOAD * Inline

[

Val

12345

];

Re: Sum and Division in Load/Select without Grouping ?

Joseph_Musekura — Sat, 24 May 2014 06:53:08 GMT

Hi,

A possible way is to format the values as text by using Text() during reload.

Load text(F1) as F1, F2 inline [
F1, F2
1, a
01, b
0002, c
000003, d
4, f

]

a problem with this approach is that a value formatted with Text() will not be used in calculations.

Regards

Re: Sum and Division in Load/Select without Grouping ?

Sun, 25 May 2014 15:10:50 GMT

Thanks Anand.

Can you please see my new question in forum ?

"Issues in the Comparision of two fields ?"

Thanks for your kind help.