topic autogenerate function in QlikView

autogenerate function

zet — Sat, 16 Nov 2024 17:41:07 GMT

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

Re: autogenerate function

chrismarlow — Mon, 21 Dec 2020 14:26:07 GMT

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.

Re: autogenerate function

zet — Mon, 21 Dec 2020 14:41:08 GMT

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

Re: autogenerate function

zet — Mon, 21 Dec 2020 15:04:19 GMT

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 ?

Re: autogenerate function

chrismarlow — Mon, 21 Dec 2020 15:10:02 GMT

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.