Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vanessa_loschi
Contributor II
Contributor II

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

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;

Labels (2)
1 Solution

Accepted Solutions
Saravanan_Desingh

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

View solution in original post

5 Replies
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
Contributor II
Contributor II
Author

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

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

vanessa_loschi
Contributor II
Contributor II
Author

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