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

[#1]

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,

[#2]

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

AJ

[#3]

I did try

Load

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

but it errored out as well

[#4]

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.

[#5]

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

[#6]

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.

[#7]

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 😕

[#8]

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.

[#9]

How would I try that?

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

[#10]

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