Hi, can anybody tell me why this script loads only data from 19th of each month to end of month?
For example, it loads from 19/2/2021 to 28/2/2021, from 19/3/2021 to 31/3/2021, from 19/4/2021 to 31/4/2021 and so on.
let v_CreateTime = date(QvdCreateTime('$(vPath)MyQVD.qvd'),'DD/MM/YYYY'); Trace $(v_CreateTime); // (it's 19/2/2021) NoConcatenate TempDate: load Distinct date(floor(mydate),'DD/MM/YYYY') as Date where date(floor(mydate),'DD/MM/YYYY')>'$(v_CreateTime)';
SQL SELECT mydate FROM Table1 where date_format(mydate,"%d/%m/%Y")>'$(v_CreateTime)';
LET NumRows=NoOfRows('TempDate'); trace $(NumRows); For n=1 To $(NumRows)
let vDate = Peek('Date',$(n)-1,'TempDate'); trace $(vDate);
LOAD id, size;
SQL select tab1.`id`,tab1.`mydate`,tab2.`id_fk` tab2.`size` Table1 tab1 inner join Table2 tab2 on tab1.`id`=tab2.`id_fk` where date_format(tab1.`mydate`,"%d/%m/%Y")='$(vDate)';
TotalSize: LOAD id, sum(size) as "TotalSize" resident TempSize group by id;
mydate is a timestamp in the db. I searched and found that to be able to compare with format like 'DD/MM/YYYY' i have to use date_format(datefield,"%d/%m/%Y") in MySQL. I already declared v_CreateTime as 'DD/MM/YYYY' , even floored it!
I see your point @Christian_R , although i don't see how they can't compare in this "where" part.