topic How to pass column() value to an Expression with set analysis in QlikView

How to pass column() value to an Expression with set analysis

Mon, 25 Jul 2011 15:19:08 GMT

Hi, i'm new in QV and i need do this:

Im loading a table inline with 3 fields

Cuenta:

LOAD * INLINE [

IdCuen, NombreCuen, Valor

1, Val1, 10

2, Val2, 20

3, Val3, 30

];

When i working with a pivot table, im creating an expression with the value of the Valor Field. So, i need to pass this value to a second expression wih set analysis like this:

Sum({<[Cuenta N3] = {$(=Column(1))}>} MOVIMIENTO)

but not working.

What im doing wrong??

I know that do the expression like Sum({<[Cuenta N3] = {$(=10)}>} MOVIMIENTO) its easy, but im trying to do more generic, load data from script.

Thanks

How to pass column() value to an Expression with set analysis

Mon, 25 Jul 2011 15:44:15 GMT

Hi,

If you want to be more generic, you can use a varible instead of column(1), so if vVariable has your expression in column(1) the result would be: Sum({<[Cuenta N3] = {'$(vVariable)'}>} MOVIMIENTO)

Hope this helps

Regards!

How to pass column() value to an Expression with set analysis

Mon, 25 Jul 2011 16:08:41 GMT

Hi,

Thanks for responding, had also thought of using variables, but if I have 100 records would have to use 100 variables? and would load these variables from the script? with the LET command?

Regards!

Re: How to pass column() value to an Expression with set analysis

Mon, 25 Jul 2011 16:16:25 GMT

Something like this, julianortiz?

Cuenta:

LOAD * INLINE [

IdCuen, NombreCuen, Valor

1, Val1, 10

2, Val2, 20

3, Val3, 30

];

let noRows = NoOfRows('Cuenta')-1;

for i=0 to $(noRows)

let v=peek('Valor',$(i),'Cuenta');

//Sum Here with variable

How to pass column() value to an Expression with set analysis

Mon, 25 Jul 2011 20:08:26 GMT

Thanks for respond rhedgpeth.

I tried your code, but only create one variable "v", and load the last number that the for take (in this case 30). In this point the question is, how to create variables dinamically?

Regards!

Re: How to pass column() value to an Expression with set analysis

Mon, 25 Jul 2011 20:33:36 GMT

julianortiz,

The for loop iterates through each individual value of Valor in the table. Because of this the last value in the table (30) will be assigned to the variable, v, when the loop ends.

Are you wanting to store the summations in a separate variable ultimately? If so, I would try something like the following. I'm only taking a guess at this, as I've never actually tried it, so the syntax may need a little tweaking:

Cuenta:

LOAD * INLINE [

IdCuen, NombreCuen, Valor

1, Val1, 10

2, Val2, 20

3, Val3, 30

];

NewSumTable:

LOAD

Sum({<[Cuenta N3] = {Valor}>} MOVIMIENTO) AS Sum,

RESIDENT

Cuenta

However, if you're wanting to store all of the values for the "Valor" column then you'll want to store those in their own table or array. Though this would be unnecessary as you already have those values in the original table.

Cuenta:

LOAD * INLINE [

IdCuen, NombreCuen, Valor

1, Val1, 10

2, Val2, 20

3, Val3, 30

];

NewValorTable:

LOAD

Valor

RESIDENT

Cuenta

Re: How to pass column() value to an Expression with set analysis

timanshu — Tue, 07 Apr 2015 11:55:58 GMT

Hi,

it will work without set expression.

Write Sum(MOVIMIENTO) , for first row this means calculate sum where Valor = 10.

If you have taken expression other than just Valor, then you can use

if([Cuenta N3] =Column(1), Sum(MOVIMIENTO),0)

It will work fine.