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

[stevietm]

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;

  NEXT

NEXT

[sunny_talwar]

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;

.

.

.