Month Loop problem

[stevietm]

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;

  NEXT

[sunny_talwar]

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

[stevietm]

Hi

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

Thanks

[sunny_talwar · Accepted solution]

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

NEXT

[stevietm]

That worked, thanks.

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

Thanks

[sunny_talwar]

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;

[stevietm]

It is set to numbers.

[sunny_talwar]

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

[swuehl]

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

  NEXT

[stevietm]

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