I'm having troubles with dates, again... this time is about date format or so I presume. This is the problem:
I have a query that loads data from an ORACLE DataBase, in this query I use this syntax to get the dates (this is intended, i need to get them like this and it works perfectly, alone):
SELECT (other fields), TO_CHAR(DATE,'DD/MM/YYYY') as Date TO_CHAR(DATE,'DD') as Day TO_CHAR(DATE,'YYYY') as Year INITCAP(TO CHAR(DATE,'MON-YYYY')) as Month-Year
In the same Qlik script sheet I also have a query that does another select from the same table but uses a group by to group the results the way I need for another graph (again, this is intended and it works like a charm), this second query groups the result by the field TO_CHAR(DATE,'DD/MM/YYYY') as Date_Group.
For a third graph I need to make a group by of this last query's results set, hence I used the Load syntax, in this load I used the following functions to get the dates I need:
LOAD (other fields), date(min(Date_Group),'DD/MM/YYYY') as Date, date(min(Date_Group),'DD') as Day, date(min(Date_Group),'YYYY') as Year, date(monthstart(min(Date_Group)),'MMM-YYYY') as Month-Year,
Note that I used the same name for the date fields because I need to pilot those two graphs (the first and the third) together by making a selection by year, day, month etc. etc.
The problem is that the results of the load block seems to be different from the first oracle select, so in a selection field for, say, the Year I'm having multiple "2009" entries, while before adding that LOAD block I had only one. So, I suspect that every result that came out from the load block is seen as different. This puzzles me and I have no clue on how to make it work the same way as the ORACLE results
I tried forcing the format as string with the text() function first in the load block, then in the qlik selection field and then in both but that didn't solve the problem...
Not quite clear, why you do all these operations in Oracle and not in QV. Would recommend to load the datefield and do the splitting into Days etc within QV.
Well, it's kinda hard to explain, especially in english, but I'm making sheets of graphs in Qlik that needs to be piloted with the same date selection fields; I have a field for the year, one for the month and one for the day.
If I get the date field and then split it in Qlik into day, month etc. etc. I'm getting weird results when I make a selection in those fields, like I'm able to select a day but not to deselect it by clicking again on it (the other days gets grayed out but the selected one won't turn green), the same things happen with the month if the results span for more than a year. I never got that thing to work for me before (I tried hard before giving up, trust me on this).
I solved all my problems "hardcoding" the date parts in all the queries, formatted as I want them to be and calling those fields the same way so selecting a day or a month will select that same day, month etc. in all the grpahs of that sheet, without faults of any kind.
This until today, when I added that load statement. Is there a way to get those dates treated the same way? Even if it's by forcing them to be what they aren't?
