topic Re: Month Loop problem in QlikView

Month Loop problem

stevietm — Wed, 25 Nov 2020 16:16:04 GMT

I am trying to Load monthly QVD files from one QVD data source with 3 year data.

The below scrip does not want to work, can anyone tell me what i am doing wrong. The Loop works and creates the qvd's but does not return any data in the QVD files.

USAGE_SQL_EXTRACT2:

LOAD SIM_NUMBER,

USAGE_DATE,

Date((USAGE_DATE),'YYYY') as YEAR,

Date((USAGE_DATE),'MM') as MONTH,

TOTAL_REV_CALC

FROM

(qvd) ;

call create_qvd_Monthly ('USAGE_SQL_EXTRACT2','USAGE_SQL_EXTRACT2')

FOR year = 2014 to 2017

FOR month = 01 to 12

MyTable:

LOAD SIM_NUMBER,

USAGE_DATE,

YEAR,

MONTH,

TOTAL_REV_CALC

FROM

(qvd)

where YEAR = $(year) and MONTH = $(month);

STORE MyTable INTO C:\Users\Desktop\Qlikview\New Test\TrxHist$(year)$(month).qvd;

Re: Month Loop problem

sunny_talwar — Wed, 05 Apr 2017 18:39:37 GMT

Try this:

USAGE_SQL_EXTRACT2:

LOAD SIM_NUMBER,

USAGE_DATE,

Year(USAGE_DATE) as YEAR,

Num(Month(USAGE_DATE)) as MONTH,

TOTAL_REV_CALC

FROM

(qvd) ;

call create_qvd_Monthly ('USAGE_SQL_EXTRACT2','USAGE_SQL_EXTRACT2')

FOR year = 2014 to 2017

FOR month = 01 to 12

MyTable:

LOAD SIM_NUMBER,

USAGE_DATE,

YEAR,

MONTH,

TOTAL_REV_CALC

FROM

(qvd)

where YEAR = $(year) and MONTH = $(month);

STORE MyTable INTO C:\Users\Desktop\Qlikview\New Test\TrxHist$(year)$(month).qvd;

NEXT

Re: Month Loop problem

stevietm — Wed, 05 Apr 2017 18:56:20 GMT

Still the same problem, also it only creates files for 2014

Thanks

Re: Month Loop problem

sunny_talwar — Wed, 05 Apr 2017 18:57:27 GMT

You probably need two NEXTs for 2 FORs

USAGE_SQL_EXTRACT2:

LOAD SIM_NUMBER,

USAGE_DATE,

Year(USAGE_DATE) as YEAR,

Num(Month(USAGE_DATE)) as MONTH,

TOTAL_REV_CALC

FROM

(qvd) ;

call create_qvd_Monthly ('USAGE_SQL_EXTRACT2','USAGE_SQL_EXTRACT2')

FOR year = 2014 to 2017

FOR month = 01 to 12

MyTable:

LOAD SIM_NUMBER,

USAGE_DATE,

YEAR,

MONTH,

TOTAL_REV_CALC

FROM

(qvd)

where YEAR = $(year) and MONTH = $(month);

STORE MyTable INTO C:\Users\Desktop\Qlikview\New Test\TrxHist$(year)$(month).qvd;

NEXT

Re: Month Loop problem

stevietm — Wed, 05 Apr 2017 19:04:03 GMT

That worked, thanks.

But still it does not return any data in the files.

Thanks

Re: Month Loop problem

sunny_talwar — Wed, 05 Apr 2017 19:07:42 GMT

Is your MONTH field look like Jan, Feb or is it numbers like 1, 2, 3?

May be try this in case it is Jan, Feb, Mar.....

MyTable:

LOAD SIM_NUMBER,

USAGE_DATE,

YEAR,

MONTH,

TOTAL_REV_CALC

FROM

(qvd)

where YEAR = $(year) and Num(Month(Date#(MONTH, 'MMM'))) = $(month);

STORE MyTable INTO C:\Users\Desktop\Qlikview\New Test\TrxHist$(year)$(month).qvd;

Re: Month Loop problem

stevietm — Wed, 05 Apr 2017 19:17:28 GMT

It is set to numbers.

Re: Month Loop problem

sunny_talwar — Wed, 05 Apr 2017 19:18:55 GMT

Can you show a screenshot from the source for Year and Month?

Re: Month Loop problem

swuehl — Wed, 05 Apr 2017 19:21:27 GMT

Guess your table gets auto-concatenated to the original:

FOR year = 2014 to 2017

FOR month = 01 to 12

MyTable:

NOCONCATENATE

LOAD SIM_NUMBER,

USAGE_DATE,

YEAR,

MONTH,

TOTAL_REV_CALC

FROM

(qvd)

where YEAR = $(year) and MONTH = $(month);

STORE MyTable INTO C:\Users\Desktop\Qlikview\New Test\TrxHist$(year)$(month).qvd;

DROP TABLE MyTable;

NEXT

Re: Month Loop problem

stevietm — Wed, 05 Apr 2017 19:38:22 GMT

Hi Thanks a lot for the help i have managed to make it work. I made a stupid mistake, i forgot that the qvd i copied from work only had 2017 data in. been a long day....

Re: Month Loop problem

stevietm — Thu, 06 Apr 2017 09:32:20 GMT

Hi Again,

I now want to add the min and max dates as variables and create a min and max month so i can only load the max month when the data comes in. See below what am i missing.

USAGE_SQL_EXTRACT2:

LOAD SIM_NUMBER,

USAGE_DATE,

Date(([USAGE_DATE]), 'YYYY-MM-DD') as USAGE_DATE1,

Year(USAGE_DATE) as YEAR,

Num(Month(USAGE_DATE)) as MONTH,

CELL_C_TOTAL_REV_CALC

FROM

(qvd) ;

call create_qvd_Monthly (' USAGE_SQL_EXTRACT2',' USAGE_SQL_EXTRACT2');

LET vMinDate = Num(Peek('USAGE_DATE1', 0, ' USAGE_SQL_EXTRACT2'));

LET vMaxDate = Num(Peek('USAGE_DATE1', -1, ' USAGE_SQL_EXTRACT2'));

LET vToday = Num(today());

LET maxmonth = num(month($(vMaxDate)));

LET minmonth = num(month($(vMinDate)));

FOR year = 2017 to 2017

FOR month = $(minmonth) to $(maxmonth) //as MONTH 3

MyTable:

LOAD SIM_NUMBER,

USAGE_DATE,

Year(USAGE_DATE) as YEAR,

Num(Month(USAGE_DATE)) as MONTH,

CELL_C_TOTAL_REV_CALC

FROM

(qvd)

where YEAR = $(year) and MONTH = $(month);

call create_qvd_Monthly ('MyTable','USAGE_SQL_EXTRACT2_$(year)-$(month)');

//STORE MyTable INTO C:\Users\steviem\Desktop\Qlikview\New Test\TrxHist$(year)$(month).qvd;

//DROP Table MyTable;

Re: Month Loop problem

sunny_talwar — Thu, 06 Apr 2017 09:33:34 GMT

Try like this

USAGE_SQL_EXTRACT2:

LOAD SIM_NUMBER,

USAGE_DATE,

Date(([USAGE_DATE]), 'YYYY-MM-DD') as USAGE_DATE1,

Year(USAGE_DATE) as YEAR,

Num(Month(USAGE_DATE)) as MONTH,

TOTAL_REV_CALC

FROM

(qvd) ;

MinMax:

LOAD Min(USAGE_DATE1) as Min,

Max(USAGE_DATE1) as Max

Resident USAGE_SQL_EXTRACT2;

LET vMinDate = Peek('Min');

LET vMaxDate = Peek('Max');

LET vToday = Num(today());

DROP MinMax;