topic Re: MonthName is not working when I do script without LOAD statement in QlikView

MonthName is not working when I do script without LOAD statement

Anonymous — Tue, 03 Mar 2015 14:20:15 GMT

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

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

Date Field,

MonthName (Date Field) as Month,

SQL SELECT *

From Table 1;

Re: MonthName is not working when I do script without LOAD statement

JonnyPoole — Tue, 03 Mar 2015 14:25:41 GMT

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.

Re: MonthName is not working when I do script without LOAD statement

Anonymous — Tue, 03 Mar 2015 14:35:01 GMT

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?

Re: MonthName is not working when I do script without LOAD statement

Tue, 03 Mar 2015 14:36:37 GMT

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

Re: MonthName is not working when I do script without LOAD statement

giakoum — Tue, 03 Mar 2015 14:38:39 GMT

if you name the 2 fields the same, QlikView will link them automatically

load

fieldname as alias

resident etc.