topic Show Month name in QlikView

Show Month name

dhborchardt — Mon, 03 May 2010 19:05:48 GMT

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?

Show Month name

Mon, 03 May 2010 19:10:22 GMT

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';

Show Month name

dhborchardt — Mon, 03 May 2010 19:44:39 GMT

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,

Show Month name

Mon, 03 May 2010 19:54:15 GMT

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 )

Show Month name

dhborchardt — Mon, 03 May 2010 21:24:30 GMT

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.

Show Month name

johnw — Mon, 03 May 2010 21:55:50 GMT

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,
...

Show Month name

dhborchardt — Tue, 04 May 2010 21:39:48 GMT

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';

Show Month name

dhborchardt — Tue, 04 May 2010 21:41:52 GMT

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?

Show Month name

johnw — Tue, 04 May 2010 21:58:32 GMT


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.

Show Month name

rwunderlich — Tue, 04 May 2010 22:55:13 GMT


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

Show Month name

johnw — Tue, 04 May 2010 23:08:50 GMT

Ah, thanks. I could swear you've told me that before, but it apparently didn't sink in the first time. I've changed my settings. That'll simplify the process a bit.

Show Month name

dhborchardt — Wed, 05 May 2010 23:49:32 GMT

Thanks for the tip on formating.
Here is what I have so far. I am not clear on how to use the LOAD except when I am using it to pull data from Excel. I was able to format the APPLYDATE properly through the properties.
This works to get the year:
Year ([APPLYDATE]) as Year,
But this does not work to get the month
Month ([APPLYDATE]) as Month,

SET 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';

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,
ORGPATHDSCTXT,
ORGPATHTXT,
PAYCODEID,
PAYCODENAME,
PAYCODETYPE,
PCVISIBLETOUSERSW,
PERSONFULLNAME,
PERSONID,
PERSONNUM,
PREVPAYPERIODEND,
PREVPAYPERIODSTART,
STARTDTM,
([TIMEINSECONDS]/60)/60 as Hours,
TIMESHEETITEMID,
VISIBLEINRPTOPTNSW,
WAGEAMOUNT,
WFCTOTALID
FROM TimeCardData;

Show Month name

johnw — Thu, 06 May 2010 00:06:21 GMT

The load should come before the SQL select. Please see my earlier post for example code, and the explanation of why the month() function isn't working for you.