autogenerate function

[zet]

Hello,

I'm working on a script that shows the disponibility of the environnement minute per minute.

I have a database filled with this kind of informations.

Time	Quantity
14:00	25
14:03	30
14:05	15

and I want to change it like that to consider every minute even if it's not in the database:

Time	Quantity
14:00	25
14:01	0
14:02	0
14:03	30
14:04	0
14:05	15

This is my script where I check if the minute exists in the database if not I insert a new line with Quantity=0.

This solution is working but it's too slow in the execution and it failed many times with timeouts  because of the autogenerate function. Any help please ?

[DISPO]:
LOAD * FROM C:\Users\Desktop\Qvd\Dispo.Qvd(Qvd);

Do while vStartTime<=vEndTime

If IsNull(Lookup('Quantity','utc_obs','$(vStartTime)','DISPO')) THEN
Let vNbPositions=0;
Else
Let vNbPositions=Lookup('Quantity','utc_obs','$(vStartTime)','DISPO') ;
ENDIF

Let vJour=mid('$(vStartTime)',1,10);
Let vMinute= mid('$(vStartTime)',12,5);

[DISPO_MINUTE]:
Load
'$(vStartTime)' as UTC_observation,
'$(vJour)' as Jour,
'$(vMinute)' as Minute,
'$(vEnvironnement)' as Environnement,
'$(vVersion)' as Version,
'$(vSource)' as Source,
'$(Quantity)' as NbPositions,
autogenerate(1);

STORE DISPO_MINUTE INTO '$(vPathQVD)'(qvd);

// Forcing change format YYYY-MM-DD:hh:mm to DD/MM/YYYY hh:mm adding 1 minute
SET DateFormat='DD/MM/YYYY hh:mm:ss';
Let vStartTime=Date(Date#(vStartTime, 'YYYY-MM-DD:hh:mm'), 'DD/MM/YYYY hh:mm');

//Add a minute
Let vStartTime= date(Timestamp#(vStartTime,'DD/MM/YYYY hh:mm'))+(1/1440);

// format YYYY-MM-DD:hh:mm
Let vStartTime=date(vStartTime,'YYYY-MM-DD:hh:mm');

Loop

[chrismarlow · Accepted solution]

Hi,

How about doing autogenerate once to get the full set of timestamps and then joining to them, something similar to;

vStartDate='01/12/2020 00:00'
vEndDate='02/12/2020 00:00'

vMins=(Date#(vEndDate,'DD/HH/YYYY hh:mm')-Date#(vStartDate,'DD/HH/YYYY hh:mm'))*1440

data:
load 
TimeStamp(Date#('$(vStartDate)','DD/HH/YYYY hh:mm')+(RecNo()/1440)) AS DateTime
autogenerate (vMins);

left join (data)
Load
	Timestamp#(DT,'DD/HH/YYYY hh:mm') AS DateTime,
	Quantity;
load * inline [
DT,	Quantity
01/12/2020 14:00, 25
01/12/2020 14:03, 30
01/12/2020 14:05, 15
];

You may need to load your data first to get vStartDate and vEndDate & possibly do another load to get zeros not nulls in Quantity, but possibly a start point.

Cheers,

Chris.  

[zet]

Thanks @chrismarlow  for your solution I did it this way :

Let vStartTime='2020-12-18:00:00';
Let vEndTime='2020-12-18:23:59';
Let vQVD='.\QVD';

Let vTime=vStartTime;

Do while vTime<=vEndTime

Minutes:
Load * INLINE[
utc_obs
$(vTime)
] ;

SET DateFormat='DD/MM/YYYY hh:mm:ss';
Let vTime=Date(Date#(vTime, 'YYYY-MM-DD:hh:mm'), 'DD/MM/YYYY hh:mm');
Let vTime= date(Timestamp#(vTime,'DD/MM/YYYY hh:mm'))+(1/1440);
Let vTime=date(vTime,'YYYY-MM-DD:hh:mm');

loop;

store Minutes into $(vQVD)\Minutes.qvd(qvd);

Result:
NoConcatenate
Load * from $(vQVD)\Minutes.qvd(qvd);
left join (Result)
LOAD * FROM $(vQVD)\Quantity.qvd(qvd);
store Result into $(vQVD)\Result.qvd(qvd);

[zet]

I want to use your script but do you know how to delete last two zeros from every date_time because in my data this field is in this format : YYY-MM-DD:hh:mm ?

[chrismarlow]

Hi,

If I have understood what you are aksing I think it will depend on how your dates are stored, in my toy app they are strings on the load, so I can use left;

vStartDate='01/12/2020 00:00'
vEndDate='02/12/2020 00:00'

vMins=(Date#(vEndDate,'DD/HH/YYYY hh:mm')-Date#(vStartDate,'DD/HH/YYYY hh:mm'))*1440

data:
load 
TimeStamp(Date#('$(vStartDate)','DD/HH/YYYY hh:mm')+(RecNo()/1440)) AS DateTime
autogenerate (vMins);

left join (data)
Load
	Timestamp#(left(DT,16),'DD/HH/YYYY hh:mm') AS DateTime,
	Quantity;
load * inline [
DT,	Quantity
01/12/2020 14:00:12, 25
01/12/2020 14:03:07, 30
01/12/2020 14:05:43, 15
];

Cheers,

Chris.