topic Re: Why my script does not work fine? in QlikView

Why my script does not work fine?

Mon, 25 Jun 2012 19:18:45 GMT

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.

Re: Why my script does not work fine?

swuehl — Mon, 25 Jun 2012 19:46:09 GMT

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;

Re: Why my script does not work fine?

Mon, 25 Jun 2012 19:56:58 GMT

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.

Re: Why my script does not work fine?

swuehl — Mon, 25 Jun 2012 20:01:38 GMT

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!)?

Re: Why my script does not work fine?

Mon, 25 Jun 2012 21:01:33 GMT

"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.

Re: Why my script does not work fine?

swuehl — Mon, 25 Jun 2012 21:19:59 GMT

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;

Re: Why my script does not work fine?

Tue, 26 Jun 2012 11:56:13 GMT

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

I will try to do load sheet by sheet.

Thanks.

Re: Why my script does not work fine?

Tue, 26 Jun 2012 13:19:43 GMT

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.

Re: Why my script does not work fine?

christian77 — Tue, 26 Jun 2012 15:24:56 GMT

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.