topic Re: How do I pull Month and Year from a BMC SQL Timestamp in QlikView

How do I pull Month and Year from a BMC SQL Timestamp

Thu, 30 Jan 2014 19:16:31 GMT

Pulling the data displays fine as 3/17/2011 2:40:42 AM. The SQL Field attri are Timestamp 23

I have tried pre-load and in line .. below is a partial of my script. As you see the 2 "Month" tries I have commented out both return lexical errors or what ever.... I have also tried the date(floor(PBI."Last Modified Date")) as Month, with the same lexical error

Please also keep in mind I am not a DBA or programmer.. I am a network and telecom guy trying to help out and streamline processes

Any help is greatly appreciated

load dual(String,Num) as Month inline

[String,Num

Jan,1

Feb,2

Mar,3

Apr,4

May,5

June,6

July,7

Aug,8

Sept,9

Oct,10

Nov,11

Dec,12

];

ODBC CONNECT32 TO [AR System ODBC Data Source] (XUserId is TdCTRRNMBDZaWQZNeA, XPassword is KAYRJEFMSDbKWcFGUBTA);

SQL SELECT

PBI.Assignee,

PBI."Assigned Group",

PBI."Assignee Pblm Mgr",

PBI."Assigned Group Pblm Mgr" as "PM Group",

PBI."Description" as "Short Description",

PBI."Detailed Decription" as "Description",

PBI."First Name",

PBI."First Reported On",

// MONTH(PBI."First Reported On") as "Month",

PBI."Last Modified Date",

// month(Timestamp#(PBI."Last Modified Date", 'MM/DD/YYYY hh:mm:ss')) as month,

Re: How do I pull Month and Year from a BMC SQL Timestamp

Thu, 30 Jan 2014 19:32:04 GMT

Hey Chuck,

Please do a preload and use your functions there.

Table:

Load

Country,

Month(Date) as Month,

Amount;

SQL Select

Country,

Amount,

Date

From DataBase;

Edit: The code above SQL Select is called a preload and any Qlikview function can be used there.

Thanks

Re: How do I pull Month and Year from a BMC SQL Timestamp

Thu, 30 Jan 2014 19:36:55 GMT

I did try

Load

MONTH(PBI."First Reported On") as "Month";

but it errored out as well

Re: How do I pull Month and Year from a BMC SQL Timestamp

Thu, 30 Jan 2014 19:48:15 GMT

Do you mind putting the quotes outside the entire field name like "PBI.First Reported On" for all the fields where there are special characters.

Re: How do I pull Month and Year from a BMC SQL Timestamp

Thu, 30 Jan 2014 19:55:03 GMT

I tried and it doesnt work... it doesnt work on the normal SQL Select if I move or change the ""

Re: How do I pull Month and Year from a BMC SQL Timestamp

Thu, 30 Jan 2014 20:14:32 GMT

Since you said it worked fine when you tried it without the Month function, I would suggest storing the table in a qvd and then read from the QVD and then use the function there. Just trying different stuff until we crack this down. Just do a limited load with like 100 rows.

Re: How do I pull Month and Year from a BMC SQL Timestamp

Thu, 30 Jan 2014 20:21:43 GMT

Ok but I have a question... if I load the timestamp fields in another file and I refresh the main page will it also reload the timestamp page?? The reason I ask is I am setting this up for a realtime dashboard for problem tickets and if it isnt able to update from clicking the Update button it will confuse people 😕

Re: How do I pull Month and Year from a BMC SQL Timestamp

Thu, 30 Jan 2014 20:26:16 GMT

Then in that case store and read from the qvd in the same QVW file where you are reading from the DB. If you have two different qvws then both has to be reloaded for the data to be updated.

Re: How do I pull Month and Year from a BMC SQL Timestamp

Thu, 30 Jan 2014 20:28:44 GMT

How would I try that?

On Thu, Jan 30, 2014 at 3:27 PM, Ajay Prabhakaran

Re: How do I pull Month and Year from a BMC SQL Timestamp

Thu, 30 Jan 2014 20:49:01 GMT

Like Example:

Customer:

Load *;

SQL Select *

From DB;

Store Customer into Customer.qvd (qvd);

Drop Table Customer;

Customer:

Load *, Month([PBI.First Reported On]) as Reported_Month

From Customer.qvd (qvd);

For limited load:

Go to Edit Script > Debug(in the top) > Limited Load (check box) > enter 100 then Run