Discussion Board for collaboration related to Creating Analytics for QlikView.
I have the following issue, I have to increase the date by 1 when the time is > 23:59:59.
01/08/2011 00:00:23 needs to be 02/08/2011 00:00:23
I have the following expression but this changes al the dates:
if(NotLiveTxStart > '23:59:59', NotLiveTxStart +1, NotLiveTxStart)
What is missing here to get ONLY the dates higher than 23:59:59 to be increased?
Go to Solution.
After all the best solution was to do this on database level. Qlikview uses a view to get the data, so I used a CASE statement there to solve this.
Thanks for the help!
i didnt quite under stand what you want
may be you can explain better here are some dates tell me what the the date should be
If the date is 01/08/2011 00:00:23 it should change to 02/08/2011 00:00:23
So the time stays the same but instead of august 1 it should be august 2.
well actuly every date expet to the 30/12/1899 is bigger then 23:59:59
you are checking dates agianst hours it not the same number
and 00:00:23 isnt bigger then 23:59:59it smaller
But how do I solve this?
can you post an excel table with example of the resaults you want
i dont realy understand what do you want to achive
Let me first try to explain further.
It's about television shows that are archived after they have been broadcasted.
Say for example that a television show is scheduled for broadcast at August 1 23:59:00.
It actually starts at 00:00:20. That is technically August 2 early in the morning.
The data I now receive says August 1 00:00:20 which is earlier than the broadcast time of August 1 23:59:00.
To get accurate results in the report the date has to change to the next day when the actual start is after 23:59:59. Since it is in reality the next day.
ok good explantion
but do you have in the record both the schedule time and the actual time
i think your expression should be somthing like this :
if(floor(NotLiveTxStart )=floor(ScheduleTime) and ( NotLiveTxStart -floor(NotLiveTxStart ))< ( ScheduleTime-floor(ScheduleTime)), NotLiveTxStart +1, NotLiveTxStart)
Yes and the schedule time should stay the same regardless of the actual time.
One suggestion would be, As I understood you just want to add a Day to your date and nothing else should change. Then you need to separate the date with format and where its Day add +1 and try around this.
some thing like
if(Mydate = 'DD/MM/YYYY', Day(Mydate, 'DD'+1), MyDate)
Hope this helps.