Why my script does not work fine?

[#1]

I have to create tables for each month with this script:

set varMonth = 'FEBMARAPRMAY';

let i = 1;

set vMonth = '';

for a=1 to 4

           let vMonth = mid($(varMonth),$(i),3);

          TMN_CHAMADAS_ACUM_$(vMonth):

          LOAD

                    TMN_Date,

                    TMN_Hour,

                    interval(rangesum(peek(TMN_Period_Acum),TMN_Period)) as TMN_Period_Acum

          Resident TMN_CHAMADAS Where TMN_Mes = $(vMonth);

          let i = ($(i)*3) + 1;

next;

----------------------------------

I would like create dynamic tables but the script says that the variable vMonth is null.

What am I doing wrong?

Thanks.

[swuehl]

Rodrigo,

I think you need to use single quotes twice around the $(varMonth) and $(vMonth):

set varMonth = 'FEBMARAPRMAY';

let i = 1;

set vMonth = '';

for a=1 to 4

           let vMonth = mid('$(varMonth)',$(i),3);    //use single quotes around $(varMonth)

          TMN_CHAMADAS_ACUM_$(vMonth):

          LOAD

                    TMN_Date,

                    TMN_Hour,

                    interval(rangesum(peek(TMN_Period_Acum),TMN_Period)) as TMN_Period_Acum

          Resident TMN_CHAMADAS Where TMN_Mes = '$(vMonth)';  //use single quotes around $(vMonth)

          let i = ($(a)*3) + 1; // I think you need to use $(a) instead of $(i) here

next;

[#3]

Hum...

That's right.

But... it created just one table with 0 rows... with no data!!!

I think that it does not know about the clausula WHERE with quotes around....

Thanks.

[swuehl]

I think the single quotes should be ok. Have you tried to use the debug mode from script editor and step through your code?

Are you sure that TMN_Mes contains values like FEB, MAR, APR, MAY (case sensitive!)?

[#5]

"Are you sure that TMN_Mes contains values like FEB, MAR, APR, MAY (case sensitive!)?"

You're right!!!

I'd ajusted and it runs ok.

But... it creates just only table... and it does not make what I want...

I want to acumulate a column with period time used by calls, but to change the acumulate by month.

And I have no idea to do it...

Thanks,

Rodrigo.

[swuehl]

Your tables have all the same field names, so there will be an automatic concatenation. Even if you create separate tables, the fields would be all linked to each other. That's probably not what you want either.

You want to reset the accumulation by Month? Then try something like

Acc:

LOAD TMN_Date,

     TMN_Hour,

     interval(rangesum( if(Peek(TMN_Mes) = TMN_Mes, peek(Accumulated)),[Period in Use])) as TMN_Period_Acum

resident TMN_CHAMADAS order by Date;

[#7]

Thanks for your help, but it does not work...

I will try to do load sheet by sheet.

Thanks.

[#8 · Accepted solution]

I did!!

The correct scrip to do is:

let varMonth = 'FevMarAbrMai';

let i = 1;

for a=1 to 10 step 3

       let vMes = mid(varMonth,$(i),3);

       TMN_CHAMADAS_ACUMULADO:

       LOAD

             TMN_Data & TMN_Hora as TMN_1,

             TMN_Ano_Num_Mes as TMN_Ano_Num_Mes1,

             TMN_Per_Facturado as TMN_Per_Facturado1,

             //TMN_Hora,

             //interval(rangesum(peek(TMN_Per_Facturado_Acumulado1),TMN_Per_Facturado)) as TMN_Per_Facturado_Acumulado1,

             if(previous(TMN_Ano_Num_Mes) = TMN_Ano_Num_Mes,interval(rangesum(peek(TMN_Per_Facturado_Acumulado1),TMN_Per_Facturado)),TMN_Per_Facturado) as TMN_Per_Facturado_Acumulado1

             //numsum( B, peek( 'Bsum' ) ) as Bsum

       Resident TMN_CHAMADAS Where TMN_Mes = '$(vMes)';

       let i = $(i) + 3;

next;

With previous() function work fine now.

Thank you so much for your help.

Rodrigo.

[christian77]

Hi Rodrigo:

If your tables have the same structure and they are the fact tables, concatenate them.

Add an artificial field when loading to identify each one.

That is the best.

QV simplifies.