topic Re: How to Add More Values within same Calculated Column in QlikView

How to Add More Values within same Calculated Column

Wed, 01 Jul 2015 10:43:43 GMT

Hi All,

Below is a Sample Data i have:

Names	Values	Values2
jk8912	10	20
Nami001	-	100
mo024	2000	4000
hon343	-	50
brt110	2500	5000

Output that i want

Names	Values
jk8912	12
Nami001	50
mo024	2000
hon343	25
brt110	2500

How to Calculate only Rows that are Null and Assign 50% of a number we get on (Values2 Column), and it must not affect existing Numbers under (Values column).

Re: How to Add More Values within same Calculated Column

swuehl — Wed, 01 Jul 2015 10:49:33 GMT

Maybe like

LOAD Names,

if(len(trim(Values)), Values, [Values2] / 2) as Values

FROM YourTableSource;

Re: How to Add More Values within same Calculated Column

sinanozdemir — Wed, 01 Jul 2015 10:49:51 GMT

I think you can do it like this:

If(Len(Values) > 0, Values, Values ÷ 2)

Hope this helps.

Re: How to Add More Values within same Calculated Column

Anonymous — Wed, 01 Jul 2015 10:50:43 GMT

Hi,

only a thing, you want 50% of Value2 column onl if the column 1 is null but in your output the first row is 12.

This should is 10?

Please explain.

Reagrds

Re: How to Add More Values within same Calculated Column

MayilVahanan — Wed, 01 Jul 2015 10:51:48 GMT

Try like this

LOAD Names, If(Len(Trim(Values))=0, Values2/2, Values) as Values, Values2 INLINE [

Names, Values, Values2

jk8912, 10, 20

Nami001, , 100

mo024, 2000, 4000

hon343, , 50

brt110, 2500, 5000

];

Re: How to Add More Values within same Calculated Column

sunny_talwar — Wed, 01 Jul 2015 10:52:37 GMT

Try this script:

Table:

LOAD *,

If(Len(Trim(Values)) > 0, Values, 0.5* Values2) as [New Values];

LOAD Names,

If(IsNum(Values), Values) as Values,

Values2

FROM

[https://community.qlik.com/thread/170764]

(html, codepage is 1252, embedded labels, table is @1);

Output:

Re: How to Add More Values within same Calculated Column

avinashelite — Wed, 01 Jul 2015 10:52:42 GMT

try like this

in expression:

if(len(values)<=0,Values2/2,values)

In script

LOAD Names,

if(len(values)<=0,Values2/2,values) as Values,

value2

from table;

Re: How to Add More Values within same Calculated Column

Wed, 01 Jul 2015 11:00:01 GMT

Hi Gallina,

my mistake it was sup to be 10.

Thanks.

Re: How to Add More Values within same Calculated Column

Wed, 01 Jul 2015 11:01:22 GMT

Hi,

Again is this issue possible to Use on Front End? than Back End?

Thanks.

Re: How to Add More Values within same Calculated Column

sunny_talwar — Wed, 01 Jul 2015 11:02:35 GMT

Yes, check Avinash R‌‌ solution, he has provided you with the front end solution as well.

Re: How to Add More Values within same Calculated Column

sunny_talwar — Wed, 01 Jul 2015 11:04:24 GMT

Front end solution:

Re: How to Add More Values within same Calculated Column

Wed, 01 Jul 2015 11:10:36 GMT

Hi Avinash,

so the problem is now my Column is a Calculated one,

so how do i calculate for both

e.g: Sum(Values)

ur Solution:

if(len(values)<=0,Values2/2,values)

so how do i add the to methods in?

Thanks

Re: How to Add More Values within same Calculated Column

avinashelite — Wed, 01 Jul 2015 11:17:31 GMT

Hi,

try the in expression level or else , in the script create two separate columns like:

LOAD Names,

if(len(values)<=0,Values2/2,values) as Values_without_Null,

values,

value2

from table;

so if you don'e want to show null values then use Values_without_Null column , when you want to calculate the sum

then use values column sum(values) so that both the things are well separated

Re: How to Add More Values within same Calculated Column

Wed, 01 Jul 2015 11:23:27 GMT

Hi,

this what i've applied:

if(len(values)<=0,value2/2,values)

and Hidden the Nor Summation Column and Activate this one to automatically assign Values.

this works.

Thanks.

Re: How to Add More Values within same Calculated Column

avinashelite — Wed, 01 Jul 2015 11:24:51 GMT

cool, if you have got the answer can you please mark it as answered and close this thread