Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all.
I have two date fields in my qvd in two different tables, in the form of date(floor([date field timestamp 1],'DD/MM/YYYY')) as datefield1 and date(floor([date field timestamp 2],'DD/MM/YYYY')) as datefield2 and also master calendars for them both.
Now i was asked to use the date picker extension but only recognizes datefield1 and datefield1MonthYear (from master calendar). Datefield2 is ignored completely.
what should i do? I tried changing the SET variable, i tried TAG field datefield2 as '$date', none works.
Have you confirmed that the field in question only contains valid dates? Note that to be recognized as a date, it needs to be a dual-type field with underlying numeric values, so you should be able to get results for every row of data using both Num([timestamp 2]) and text([timestamp 2]) for non-null values.
Hi and thank you @Or 🙂 So what is exactly the cross check i should do for timestamp2?
Check for null values and see if this field gives results for both Num([timestamp 2]) and text([timestamp 2]) ?
And next how should i treat it for date picker to work?
I would try, in script (hopefully syntax is correct, I can't test)
If(len(num([timestamp 2]))=0 AND NOT IsNull([timestamp 2]),'Not a number') as IsThisANumber
If any values of 'Not a number' are returned, those are probably not valid dates since they lack numeric representation.
There's probably an easier way to do this, but I think this one would work...
@Or , all timestamp2 are '-' for this command!
That suggests one of two things:
1) I made a mistake in the formula
2) They are not dates
Try explicitly formatting them as dates / converting them to dates using date() or date#() (or the respective timestamp function if that's needed) and see if that helps.
But @Or , i though this in my script that i did : "date(floor([date field timestamp 1],'DD/MM/YYYY')) as datefield1 and date(floor([date field timestamp 2],'DD/MM/YYYY')) as datefield2" that i mentioned in my first post , declares that datefield2 is a date.... and looks like it in a filter, floored and all.
I'm confuuuuuuuuuuuuuuuuuused
Indeed, if they are floored correctly you would expect them to be legally numeric.. and thinking about it, there are easier ways to check if it's a legal date - just see if it responds correctly to any date-specific command such as Year(datefield2).
If it responds correctly to date-specific functions, and you've tagged it as $date (sometimes necessary, particularly if your date range might include dates in the distant past or distant future), and the calendar still doesn't pick it up, I'm stumped.