Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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

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

6 Replies
Not applicable

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

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!

Not applicable

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

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!

Not applicable

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

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

next

Not applicable

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

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!

Not applicable

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

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

timanshu
Contributor III

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

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.

Community Browser