Show Month name

[dhborchardt]

I have a list box showing the months. I can get it to display numbers for example 1 2 3 4 5 (instead of Jan, Feb, Mar, April etc). I can also get them to display 1/1/2010. How do I get it to display Jan, Feb, Mar, Apr etc?

[#2]

Hi,

create an expression in your listbox : Month(Date) for example.

before check in the script you have :

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

jj

[dhborchardt]

The set MonthNames is correct in the script.

In my script I have:

SQL

Shoul this work? Year works fine but not month. Not sure were to include the expresion in the list box properties. I am new at this. Thanks

SELECT
APPLYDATE,
Year ([APPLYDATE]) as Year,
Month ([APPLYDATE]) as Month,

[#4]

Strange because your script looks good. Are you sure Applydate date is a date ?

Edit the listbox properties, go to the field section, edit <expression> then enter Month( APPLYDATE )

jj

[dhborchardt]

Data from the DB table (ApplyDate) shows the field to contain a date in this format: x/x/xxxx or 1/9/2010.

But when I pull this data into QV it show up as: 1/9/2010 12:00:00 AM. Its adding the time.

Do I need to make some changes on how QV pull the data in?

Even when I just create a list box for ApplyDate without any change to the script it still does this. It also does this to all other dates I create listboxes for QV on.

[johnw]

Year() and month() functions need to be applied on the QlikView side (the load), not the SQL side (the select). As for QlikView adding the time, it sounds like it is interpreting this as a timestamp instead of a date. We can be explicit about calling it a date and what format it's in as we read it in. Something like this:

LOAD
...
date(date#(APPLYDATE,'M/D/YYYY')) as ApplyDate
year(date#(APPLYDATE,'M/D/YYYY')) as ApplyYear,
month(date#(APPLYDATE,'M/D/YYYY')) as ApplyMonth,
... ;
SQL
SELECT
...
APPLYDATE,
...

[dhborchardt]

Isn't the Load for loading files like CSVs? Below is the script.

SET

ODBC CONNECT TO [MS Access Database;DBQ=N:\Data\McGladrey\ADP\AdpToFastHTest.mdb];
SQL SELECT ABBREVIATIONPCCHAR,
ADDTOTCETOTSW,
ADJUSTEDAPPLYDATE,
APPLYDATE,
Year ([APPLYDATE]) as Year,
Month ([APPLYDATE]) as Month,
CHILDPAYCODEID,
CURRPAYPERIODEND,
CURRPAYPERIODSTART,
DISPLAYORDERNUM,
EMPLOYEEID,
ENDDTM,
HOMEACCOUNTSW,
ID,
ISMONEYAMOUNTSW,
LABORACCTID,
LABORACCTNAME,
LABORLEVELDSC1,
LABORLEVELDSC2,
LABORLEVELDSC3,
LABORLEVELDSC4,
LABORLEVELDSC5,
LABORLEVELDSC6,
LABORLEVELDSC7,
LABORLEVELNAME1,
LABORLEVELNAME2,
LABORLEVELNAME3,
LABORLEVELNAME4,
LABORLEVELNAME5,
LABORLEVELNAME6,
LABORLEVELNAME7,
MONEYAMOUNT,
NEXTPAYPERIODEND,
NEXTPAYPERIODSTART,
NOTPAIDSW,
RGPATHDSCTXT,
ORGPATHTXT,
PAYCODEID,
PAYCODENAME,
PAYCODETYPE,
PCVISIBLETOUSERSW,
PERSONFULLNAME,
PERSONID,
PERSONNUM,
PREVPAYPERIODEND,
PREVPAYPERIODSTART,
STARTDTM,
([TIMEINSECONDS]/60)/60 as Hours,
TIMESHEETITEMID,
VISIBLEINRPTOPTNSW,
WAGEAMOUNT,
WFCTOTALID

FROM

TimeCardData; ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

[dhborchardt]

Funny thing is happening when I paste script to this forum. It flips some of the code around and puts extra spaces in. Whats up with that?

[johnw]

dhborchardt wrote:Funny thing is happening when I paste script to this forum. It flips some of the code around and puts extra spaces in. Whats up with that?

Hmmm, seems like the sticky has disappeared, but officially, QlikTech requests that you use [ code ] [ /code ] tags around your code (sans spaces). However, if you do this, code can scroll off the screen where you can't see it, and if I cut and paste from a code block like this into notepad or QlikView, it removes all the new line characters.

So what I do, and what I suggest, is to copy from QlikView into Notepad first, which will remove all the formatting. Then copy from there into your post. Finally, select the code block, and change the font to Courier New. Code posted like that can all be seen (since it will wrap) and can be copied directly into QlikView.

[rwunderlich]

John Witherspoon wrote:is to copy from QlikView into Notepad first, which will remove all the formatting

An alternative is to uncheck "Copy as RTF" in Settings, User Preferences, Editor. With that option unchecked, the text copied to the clipboard will be unformatted.

-Rob