script loads data up to a certain date each month

[ioannagr]

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

TempSize:
NoConcatenate

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;

DROP TABLE TempSize;
next n;
drop table TempDate;

[ioannagr]

Anyone? 🙂

[MayilVahanan]

Hi ioannagr,

Might be "tab1.`mydate`" table contains 19th to 30th date alone.

[ioannagr]

Hi @MayilVahanan , no, this table contains all dates in all months!

One thing i noticed is

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

if i only include where in sql select it loaded all dates even before v_CreateTime, so i should add where in load too.

I'm clueless here 🤔

[MayilVahanan]

Hi 

Might be, the format may be difference. Check in DB with same format and verify it.

Instead of convert date format, try like below

floor(mydate) > Floor('$(v_CreateTime)');

[ioannagr]

@MayilVahanan  in load or in sql select? 🤔

[MayilVahanan]

In Load statement..

For SQL, please check  the below statement in database whether date format is correct or not?

 where date_format(mydate,"%d/%m/%Y")>'$(v_CreateTime)';

[ChristianRiegel]

just try this for your tempdate Table:

TempDate:
Load
Date(MinDate + IterNo() -1 ) AS Date
While (MinDate + IterNo()- 1) <= Num(MaxDate);

Load
Min(mydate) AS MinDate,
Max(mydate) AS MaxDate
;

SQL SELECT mydate
FROM Table1
where date_format(mydate,"%d/%m/%Y")>'$(v_CreateTime)';

[ioannagr]

@ChristianRiegel  hello, tried your code, it starts loading all the dates possible, not just after the v_CreateTime. 🤔

[ChristianRiegel]

it seems that the SQL dont match the where syntax. please check if the statement will work.

I think  you have to fomate the String ('$(v_CreateTime)') do a valid Date format.