Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am joining tables (LEFT JOIN). When I use MonthName (Date Field) - I should get the result as Jan 2014 but I am getting only Jan.
My Script sample:
SELECT
A,
B,
C,
Date Field,
MonthName (Date Field) as Month,
From Table 1;
But when I do the scripting using LOAD statement like below the result is Jan 2014... Why? Help required please...
LOAD
A,
B,
C,
Date Field,
MonthName (Date Field) as Month,
SQL SELECT *
From Table 1;
if you name the 2 fields the same, QlikView will link them automatically
load
fieldname as alias
resident etc.
The script syntax doesn't look syntactically correct , but i'm guessing that if you put the monthname() in the select statement the function is processed by the database and the database function may give you a different result than the qlikview function of the same name. LOAD is strictly qlikview , so if you can't do the manipulation in the SQL you can do it in qlikview in the load.
Ok but I dont know how to join the 2 tables by using LOAD statement. But I did it in the SELECT alone style. Please see below the script I am using currently.
ODBC CONNECT TO OTRS;
SELECT
otrs.ticket.tn as "Ticket #",
otrs.ticket.ticket_state_id,
Month (otrs.ticket.create_time)as "Month",
Year (otrs.ticket.create_time) as Year,
otrs.ticket_state.name as "Status"
FROM otrs.ticket
LEFT JOIN otrs.ticket_state
ON otrs.ticket.ticket_state_id=otrs.ticket_state.id;
Table 1 is otrs.ticket
Table 2 is otrs.ticket_state
table 1 the column ticket_state_id = table 2 column id
As a result the ticket # from table 1 will link to status from table 2
How should I do the above by using LOAD statement?
1. Check your field in SQL is stored as a Date Time Field and not text
2. Check format in Settings > Document Properties> Number if that is set to your liking
If 1 and 2 fail try the following:
Date (MonthName (Date Field), 'MMM-YYYY') as Month,
Regards
RL
if you name the 2 fields the same, QlikView will link them automatically
load
fieldname as alias
resident etc.