Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
zet
Contributor III
Contributor III

autogenerate function

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.

TimeQuantity
14:00 25
14:03 30
14:0515

 

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

TimeQuantity
14:0025
14:010
14:020
14:0330
14:040
14:0515

 

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

 

 

1 Solution

Accepted Solutions
chrismarlow
Specialist
Specialist

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.  

View solution in original post

4 Replies
chrismarlow
Specialist
Specialist

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.  

View solution in original post

zet
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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 ?

mzitouni_0-1608562987227.png

 

chrismarlow
Specialist
Specialist

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.