Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

guitartrova
Contributor III

Load current month data with do while

I am using a do while to try to bring data for the current month, but only upload data until the previous month I need your help please.


/* >> LOADA DATA PER PERIODS ========================================= */

LET vPeriodo = (YEAR(TODAY())) & '0'; // CURRENT A YEAR

LET vMesActual = NUM(MONTH(TODAY())); // CURRENT ACTUAL

LET vMesProximo = NUM(MONTH(TODAY())+1); // NEXT MONTH

LET vI = 7; // INITIAL MONTH

DO WHILE vI <= vMesActual

LET vCiclo = vPeriodo & vI; // BUILD YEAR-MONTH

TRACE $(vCiclo);

LET vCiclo = vCiclo & '01'; // BUILD YEAR-MONTH-FIRSTDAY

LOAD

SELECT * FROM TABLE

WHERE DATE =  TRUNC(LAST_DAY('$(vCiclo)'))

LET vI = vI + 1;

LOOP;

1 Solution

Accepted Solutions
MVP
MVP

Re: Load current month data with do while

Sorry - I was a bit quick... the code should be like this instead:

vDate = Date(Date#('20180701','YYYYMMDD'),'YYYYMMDD');  

DO WHILE vDate <= Today(1) 

  /* TRACE */ SELECT * FROM TABLE WHERE DATE = TO_DATE('$(vDate)','YYYYMMDD');  

  LET vDate = Date( vDate+1 ,'YYYYMMDD'); 

LOOP

13 Replies
sunilkumarqv
Valued Contributor II

Re: Load current month data with do while

but only upload data until the previous month I need your help please.


if DATE = 2018-08-27


Let  vPrevmonth= Year(today())&'-'&Month(AddMonths(Today(),-1))&'-'&Day(today())

LOAD

SELECT * FROM TABLE

WHERE DATE <=$(vPrevmonth)



DavidŠtorek
Contributor III

Re: Load current month data with do while

Hi,

is there any special reason why you need to use loop?

I don't know your date format but if you want to load all data excluding current month I would do it as following

Let vMaxDate= MonthStart(Today());

Select * ...

Where Date < Date '$(vMaxDate)';

guitartrova
Contributor III

Re: Load current month data with do while

Maybe I interpreted badly, I need the data until the current month July, with the script that I have only shows me until June.

ciclo.png

DavidŠtorek
Contributor III

Re: Load current month data with do while

And you have data for longer period than until now?

Let vMaxDate= Year(Today())&If(Num(Month(Today()))>9,Num(Month(Today())),0&Num(Month(Today())));

Select * ...

Where Date <= '$(vMaxDate)';

MVP
MVP

Re: Load current month data with do while

The reason why you only get data until June is that the date you are filtering on is the last day of the month of each month. So for July it will be the 31st of July 2018 which hasn't arrived yet. So it will only be on the actual 31st of July 2018 that the July numbers will be available for you... unless of course there are some future values existing which I suspect is not the case....

Be aware that your code also has some logical flaws that will make it fail when you get past September since you are prepending the month number with a 0 which you shouldn't do unconditionally when you get into the last four months of the year.

MVP
MVP

Re: Load current month data with do while

Referring to my previous response here is your script modified so it caters for the logical error of always prepending month numbers with a zero:

/* >> LOADA DATA PER PERIODS ========================================= */

//LET vPeriodo = (YEAR(TODAY())) & '0'; // CURRENT A YEAR

  LET vPeriodo = (YEAR(TODAY())); // CURRENT A YEAR

LET vMesActual = NUM(MONTH(TODAY())); // CURRENT ACTUAL

LET vMesProximo = NUM(MONTH(TODAY())+1); // NEXT MONTH

LET vI = 7; // INITIAL MONTH


DO WHILE vI <= vMesActual

//  LET vCiclo = vPeriodo & vI; // BUILD YEAR-MONTH

  LET vCiclo = vPeriodo & Num(vI,'00'); // BUILD YEAR-MONTH

  TRACE $(vCiclo);

  LET vCiclo = vCiclo & '01'; // BUILD YEAR-MONTH-FIRSTDAY


  LOAD

  SELECT * FROM TABLE

  WHERE DATE BETWEEN TO_DATE('$(vCiclo)','YYYYMMDD') AND TRUNC(LAST_DAY('$(vCiclo)'));

  LET vI = vI + 1;

LOOP;

guitartrova
Contributor III

Re: Load current month data with do while

This is what I wanted in my code, now it works for me and I create data for July.


Is there a way for the data to be processed for each day of the month and arrive at the real day?


I would like to be loading the data for example July 1, July 2, July 3 .... July 31 and avoid loading the month at once.


Thank you all for helping me in these moments.

MVP
MVP

Re: Load current month data with do while

Sure

vDate = Date('20180701','YYYYMMDD');

DO WHILE vDate <= Today(1)

  SELECT * FROM TABLE WHERE DATE = TO_DATE('$(vDate)','YYYYMMDD');

  LET vDate = Date( vDate+1 ,'YYYYMMDD');

LOOP

The start day can span over years and months too since adding one for each new day will always calculate to the correct following day...

guitartrova
Contributor III

Re: Load current month data with do while

Now it does not show me the data, it loads other tables but the one that has the while is not loading anything.


I send the capture of the variables that we are using but the variable vDate shows me a weird data

variables.png

c.png

Community Browser