Qvd for every date in source data

[ioannagr]

Hello 

My code for that so far is:

MinMax:

Load

Max(date(floor(Mydates),'DD/MM/YYYY') as MaxDate,

min(date(floor(Mydates,'DD/MM/YYYY') as MinDate

SQL Select Mydates    //Mydates is a timestamp

from public.Table1;



LET MaxDate = PEEK('MaxDate', 0 , MinMax); // Store Maximum value
LET MinDate = PEEK('MinDate', 0 , MinMax); // Store Mininum value

Let vDate_i= (date(MinDate,'DD/MM/YYYY'));  //to use in loop



For i=$(MinDate) to $(MaxDate)

TempSize:
NoConcatenate

LOAD
pk,
size,
Mydates;

SQL select
t1.`pk` "pk",t1.`Mydates`, t2.`size`
from
public.Table1 t1
inner join
public.Table2 t2
on t2.`fk`=t1.`pk`
where date_format(stu.`study_datetime`, "%d-%c-%Y")='$(vDate_i)';

SizeFinal:
LOAD
pk,
sum(file_size) as Sum
resident TempSize
group by pk;

If NoOfRows('SizeFinal')>0 then
STORE Image_Size_on_Disk4 INTO [$(vPathQVD)/Image_Size_on_Disk4_$(vDate_i).qvd]; //into $(vFileName).qvd (qvd);
TRACE 'Store qvd file:'[$(vPathQVD)/Image_Size_on_Disk4_$(vDate_i).qvd];
ENDIF
drop table TempSize;

Let MinDate=date(floor((vDate_i)+1),'DD/MM/YYYY');
Next i;

However, looks like only MaxDate and MinDate are loaded.

I want to read from source data for each date found, store it in a qvd like SizeFinal_19/5/2021.qvd, SizeFinal_20/5/2021.qvd and so on separately. Also I don't want to hold on memory the table SizeFinal because it's very big (over half a billion).

Looking forward to some eye opening help!

[Kushal_Chawda]

Try changing the code like below

MinMax:
Load
Max(date(floor(Mydates),'DD/MM/YYYY') as MaxDate,
min(date(floor(Mydates,'DD/MM/YYYY') as MinDate;
SQL Select Mydates    //Mydates is a timestamp
from public.Table1;

LET MaxDate = PEEK('MaxDate', 0 , MinMax); // Store Maximum value
LET MinDate = PEEK('MinDate', 0 , MinMax); // Store Mininum value

For i=$(MinDate) to $(MaxDate)

Let vDate_i= date($(i),'DD/MM/YYYY');  //to use in loop

TempSize:
NoConcatenate
LOAD
pk,
size,
Mydates;

SQL select
t1.`pk` "pk",t1.`Mydates`, t2.`size`
from
public.Table1 t1
inner join
public.Table2 t2
on t2.`fk`=t1.`pk`
where date_format(stu.`study_datetime`, "%d-%c-%Y")='$(vDate_i)';

SizeFinal:
LOAD
pk,
sum(file_size) as Sum
resident TempSize
group by pk;

drop table TempSize;

If NoOfRows('SizeFinal')>0 then
STORE Image_Size_on_Disk4 INTO [$(vPathQVD)/Image_Size_on_Disk4_$(vDate_i).qvd]; //into $(vFileName).qvd (qvd);

drop table SizeFinal;

TRACE 'Store qvd file:'[$(vPathQVD)/Image_Size_on_Disk4_$(vDate_i).qvd];
ENDIF
let vDate_i = null();
Next i;