Parameters in Variables

[AndreFrencl]

Hi, 

I write a script in my load that change the field name, as MES_<MONTH>. Its is pass by parameters.

set vmes = 'MES_'&upper(month(addmonths(date(Date#($(InputDate),'YYYYMM')),-$1)))

if i wrote

let vmesmo= $(vmes(1)); 

trace $(vmesmo);

its works!!

but, i want put this script in my load , like:

LOAD
TRANSACAO_ID,
SUB_CANAL_ID,
CIDADE_ID,
PRODUTO_ID,
PROVEDOR_ID,
Num(Replace(MES_1_REAIS,'.',','),'#.##0,00') as $(vmes(1)),
Num(Replace(MES_2_REAIS,'.',','),'#.##0,00') as $(vmes(2)),
Num(Replace(MES_3_REAIS,'.',','),'#.##0,00') as $(vmes(3)),

......

why dont works?

regards,

André

[AndreFrencl · Accepted solution]

i just find the solution for my case... 

//money
set vmes = 'MES_'&upper(month(addmonths(date(Date#($(InputDate),'YYYYMM')),-$1)))&right(year(addmonths(date(Date#($(InputDate),'YYYYMM')),-$1)),2)&'_REAIS';
for ct = 0 to 23
let nome = 'a'&($(ct)+1);
let $(nome) = $(vmes($(ct)));
next;

//UNID
set vmes = 'MES_'&upper(month(addmonths(date(Date#($(InputDate),'YYYYMM')),-$1)))&right(year(addmonths(date(Date#($(InputDate),'YYYYMM')),-$1)),2)&'_UNID';
for ct = 0 to 23
let nome = 'b'&($(ct)+1);
let $(nome) = $(vmes($(ct)));
next;

Fato:
LOAD
TRANSACAO_ID,
SUB_CANAL_ID,
CIDADE_ID,
PRODUTO_ID,
PROVEDOR_ID,

Num(Replace(MES_1_REAIS,'.',','),'#.##0,00') as $(a1),
Num(Replace(MES_2_REAIS,'.',','),'#.##0,00') as $(a2),
Num(Replace(MES_3_REAIS,'.',','),'#.##0,00') as $(a3),
Num(Replace(MES_4_REAIS,'.',','),'#.##0,00') as $(a4),
Num(Replace(MES_5_REAIS,'.',','),'#.##0,00') as $(a5),
Num(Replace(MES_6_REAIS,'.',','),'#.##0,00') as $(a6),
Num(Replace(MES_7_REAIS,'.',','),'#.##0,00') as $(a7),
Num(Replace(MES_8_REAIS,'.',','),'#.##0,00') as $(a8),
Num(Replace(MES_9_REAIS,'.',','),'#.##0,00') as $(a9),
.........

André

[Sue_Macaluso]

@AndreFrencl Are you using QlikView or Sense?  I would like to move this into the correct product forum.  I know you found the answer and in the correct forum, many people can benefit. Thank you.

[AndreFrencl]

I use qlikview.