Expansão do $ dentro de um Load de uma Tabela Resident

[vanessa_loschi]

Boa Tarde,

Gostaria de saber se consigo usar a expansão do $ em um Load de uma Tabela Residente.

FOR a = 0 to NoOfRows('DREGER') - 1

LET vID = chr(39)&Peek('ID_DRE', $(a), 'DREGER')&chr(39);
LET vFOR = Peek('FOR_DRE', $(a), 'DREGER');

// Aqui eu carrego a fórmula na variável vFOR.

// Abaixo eu uso os campos da tabela residente como parâmetros  para a variável.

//São várias fórmulas, todas iniciadas por SUM.

Gerencial:
LOAD 

$(vID)                                                             as ID_GER,
"mês"                                                            as DT_GER,
$(vFOR("Conta13","MOVIMENTO")) as VLR_GER
Resident Balancete
Group By "mês";

next;

[Saravanan_Desingh]

Yes, you can use a $-expansion in your LOAD.

But I would like to know what you are trying to do. Can you show a sample and expected output? 

[vanessa_loschi]

Good Morning,

I have an excel file with my formulas like this:

sum(if(mid($1,1,2) = '31' or mid($1,1,2) = '32',$2)) * -1

before, i load this file:

DREGER:
Directory;
LOAD ID_DRE,
DS1_DRE,
DS2_DRE,
VAR_DRE,
FOR_DRE
FROM
$(Dir)[BASE DRE.xlsx]
(ooxml, embedded labels, table is DREGER);

FOR x = 0 to NoOfRows('DREGER') - 1

LET vNomeVar = Peek('VAR_DRE', $(x), 'DREGER');
LET vFormVar = Peek('FOR_DRE', $(x), 'DREGER');

SET $(vNomeVar) = $(vFormVar);

LET vNomeVar = Null();
LET vFormVar = Null();

NEXT x;

I tried use the formulas in a variables, but my performance was not good.

Then, i'm trying load the result of formulas directly in my script like a showed before.

If i put the formula in the load script, it's run perfectly.

Gerencial:
LOAD $(vID) as ID_GER,
$(vDS1) as vDS1_GER,
$(vDS2) as vDS2_GER,
"mês" as comp_GER,
(sum(if(mid("Conta13",1,2) = '31' or mid("Conta13",1,2) = '32',"MOVIMENTO")) * -1) as VLR_GER
// $(vFOR2("Conta13","MOVIMENTO")) as VLR_GER
Resident Balancete
Group By "mês";

The script results in the value calculated by the formula.

But with the variable, does't work.

Thanks for your help.

[Saravanan_Desingh · Accepted solution]

How are you defining the Variable with LET or SET? Since it is having expression, use SET. Don't use LET.

[vanessa_loschi]

Hi,

I had used LET, the SET worked perfectly.

I spent a lot of time trying to make it work.
Thank you so much for your time and help.

Regards

[Saravanan_Desingh]

you welcome