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

[#1]

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

[#2]

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!

[#3]

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!

[#4]

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

[#5]

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!

[#6]

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]

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.