Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MarioCenteno
Creator III
Creator 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
petter
Partner - Champion III
Partner - Champion III

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

View solution in original post

13 Replies
sunilkumarqv
Specialist II
Specialist II

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
Creator III
Creator III

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)';

MarioCenteno
Creator III
Creator III
Author

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
Creator III
Creator III

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)';

petter
Partner - Champion III
Partner - Champion III

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.

petter
Partner - Champion III
Partner - Champion III

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;

MarioCenteno
Creator III
Creator III
Author

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.

petter
Partner - Champion III
Partner - Champion III

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

MarioCenteno
Creator III
Creator III
Author

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