Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I have been trying to figure out how to solve point in time within triggers in my QV application for quite a while now.
I have got triggers set on opening my application. These are set to pick the 8am snapshot. This approach was working fine until our DW went out of sync and snapshot was instead of 8am created at 9am.
Now I would like to find a solution in case this happens again in the future. So if the snapshot at 8am is not created, then pick the 9am one. If the 8am is not created, but instead the snapshot is created at 10am pick this one...and so on.
I have tested the following solutions within my app where data 8am snapshot exists, and I have also tested these within my app_test where the 8am snapshot does not exist but the 9am snapshot exists.
1. Works fine where 8 am snapshot exist. Doesn't work for 9am snapshot exists, but 8am snapshot doesn't.
=if(min(Hour<>'8'),if(min(Hour<>'9'),'8','9'),'18')
2. Works fine where 8 am snapshot exist. Doesn't work for 9am snapshot exists, but 8am snapshot doesn't.
=if(min(Hour='8'),'8','18')
3. Doesn't work where 8 am snapshot exist. Does work where 9am snapshot exists, but 8am snapshot doesn't.
=if(min(Hour<>'8'),
if(min(Hour='9'),'9',
if(min(Hour='8'),'8','18')))
4. Doesn't work where 8 am snapshot exist. Does work where 9am snapshot exists, but 8am snapshot doesn't.
=if(min(Hour='8'),
if(min(Hour='9'),'9',
if(min(Hour='10'),'10',
if(min(Hour='11'),'11',
if(min(Hour='12'),'12',
if(min(Hour='13'),'13',
'8'))))), '18')
5. Doesn't work where 8 am snapshot exist. Does work where 9am snapshot exists, but 8am snapshot doesn't.
=if(mixmatch(min(Hour),'8','9'),'8', '9')
Managed to fix this. As the snapshots are actually in a format of Timestamp so: DD/MM/YYYY hh:mm:ss.
So I had to:
1. create a variable vMaxHour =hour(max(Timestamp(Snapshot)))
2. apply the variable to triggers set on the sheet
1. Triggers->On Activate Sheet
2. Select in Field: Field=Hour, Search String =$(vMaxHour)
Hi,
I don't understand fully, but why don't you do "if(min(hour)=8, ..."
Hope this helps
Tobias
from what I understand it seems you need a
=min(Hour)
or
=if(min(Hour)>='8' and min(Hour)<='18',min(Hour))
Managed to fix this. As the snapshots are actually in a format of Timestamp so: DD/MM/YYYY hh:mm:ss.
So I had to:
1. create a variable vMaxHour =hour(max(Timestamp(Snapshot)))
2. apply the variable to triggers set on the sheet
1. Triggers->On Activate Sheet
2. Select in Field: Field=Hour, Search String =$(vMaxHour)