Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I have connect my Qlikview to a ODBC SQL server.
Example:
SQL SELECT `date_hi`,
FROM aabridge.`Call_hi`
Then i get all the dates. The format of the dates are in "Datetime" so. "YYYY-MM-DD HH-MM-SS".
Now i want to make a select so i get the years and the month, and that should be easy so i try:
SQL SELECT datepart(yyyy, `date_hi`) AS Year,
datepart(mm, `date_hi`) as Month,
FROM aabridge.`Call_hi`
But then i get SQL error when i try to load saying:
SQL##f - SqlState: 37000, ErrorCode: 1064, ErrorMsg: [MySQL][ODBC 5.1 Driver][mysqld-4.1.20]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(yyyy, `date_hi`) AS Year,
datepart(mm, `date_hi`) as Month,
FROM aabridge' at line 1
SQL SELECT datepart(yyyy, `date_hi`) AS Year,
datepart(mm, `date_hi`) as Month,
FROM aabridge.`Call_hi`
Can someone please help me?
Thank you!
Then you should do a
LOAD date_from, Year(date_from) as Year, Month(date_from) as Month;
SQL SELECT distinct `date_from` FROM ccbridge.`Call_logg`
HIC
Hi Erik,
Do this way
[TableName]:
Load
date_hi AS OrigDate,
Date(Floor(date_hi)) AS NewDate,
Month(date_hi)) AS NewMonth,
Year(date_hi)) AS NewYear;
SQL SELECT date_hi,
FROM aabridge.Call_hi
Regards,
Sokkorn
Load the date and perform the transformation in your load script:
Load Year(ate_hi) as Year,
Month(date_hi) as Month,
etc.
sql select ....
And consider using a master calendar to link to your date field.
First of all: The SELECT statement is NOT evaluated by QlikView - it is just sent to your ODBC client and is evaluated there. So the syntax for the correct SELECT depends on the ODBC driver.
Secondly, it is better if you let QlikView define Year, Month, Day instead of doing it in the SELECT. E.g. by
Load *,
Year(date_hi) as Year,
Month(date_hi) as Month,
Day(date_hi) as Day
;
SQL SELECT `date_hi`,
FROM aabridge.`Call_hi` ;
Or even better - in a separate table:
Transactions:
SQL SELECT `date_hi`,
FROM aabridge.`Call_hi` ;
Calendar:
Load distinct
date_hi,
Year(date_hi) as Year,
Month(date_hi) as Month,
Day(date_hi) as Day
Resident Calendar;
HIC
First of all, thanks to all of you for answering so fast!
Yes, ok.
When i do it that way, loading the Year and the Month then i get no relations between the different listboxes?
When i choose for example 2013 and Jan the related data in the date_hi dont get highlighted
So the Year and Month are not related in any way which i also can se in the tableview.
If they are not related in the table viewer, you have to create this relationship: You need to include a key between your tables in your Load/SELECT statements. Post an image of your table viewer.
HIC
Instead of
Year(date_hi) as Year,
Month(date_hi) as Month,
you should have
date_hi as date_from,
Year(date_hi) as Year,
Month(date_hi) as Month,
HIC
Sorry for the unclear names here.
There is actually no Call_hi or date_hi.
This is my Load and Select, and that generates the tableviewer
LOAD Year(date_from) as Year, Month(date_from) as Month;
SQL SELECT `date_from`
FROM ccbridge.`Call_logg`
Then you should do a
LOAD date_from, Year(date_from) as Year, Month(date_from) as Month;
SQL SELECT distinct `date_from` FROM ccbridge.`Call_logg`
HIC