topic FORMATING DATE - HELP in QlikView

FORMATING DATE - HELP

Wed, 22 Sep 2010 17:24:25 GMT

I'm new to qlikview, having used this over a few days I am getting slightly frustrating with the script language. I am trying to format the month to return a Text rather than a number.

With my script below, it returns a number, not text. Can someone help????

SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DateFormat='DD/MM/YYYY';
SQL SELECT `se_AP`,
`se_DATEIMPORT`,
`se_FUND`,
`se_FundNAV`,
`se_ID`,
`se_IMPORTID`,
`se_SwapMTM`,
`se_SwapMTMFundNAVPer`,
`se_SwapMTMNotionalPer`,
`se_SwapNotional`,
Year(se_DATEIMPORT)as TYEAR,
Month (se_DATEIMPORT)as TMONTH

FROM `dbo_SwapExposure`;

FORMATING DATE - HELP

Wed, 22 Sep 2010 17:27:03 GMT

Month() returns the numeric month of the date. MonthName() should return the month abbreviation plus the year.

Left(MonthName(se_DATEIMPORT), 3) as TMONTH should give you just the month abbreviation.

FORMATING DATE - HELP

Wed, 22 Sep 2010 17:35:19 GMT

Thanks for getting back to me

Left(MonthName(se_DATEIMPORT),3) as TMONTH

I entered this in and I get an error

FORMATING DATE - HELP

Wed, 22 Sep 2010 17:39:36 GMT

What is the error? What is the format of se_DATEIMPORT?

Try entering this:

Left(MonthName(Now()), 3) As TodayMonth

I did that in a test and it worked. It could be that it is not recognizing se_DATEIMPORT as a Date. You may have to use Date() or Date#() to convert it first.

FORMATING DATE - HELP

Thu, 23 Sep 2010 08:01:12 GMT

This is the format of the date field = 22/09/2010

Im new to script writing on qlikview

How would I edit this query so it returns the abbreivation name of the month rather than number

SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DateFormat='DD/MM/YYYY';
SQL SELECT `se_AP`,
`se_DATEIMPORT`,
`se_FUND`,
`se_FundNAV`,
`se_ID`,
`se_IMPORTID`,
`se_SwapMTM`,
`se_SwapMTMFundNAVPer`,
`se_SwapMTMNotionalPer`,
`se_SwapNotional`,
Year(se_DATEIMPORT)as TYEAR,
Left(MonthName(se_DATEIMPORT),3) as TMONTH

FROM `dbo_SwapExposure`;

FORMATING DATE - HELP

Thu, 23 Sep 2010 08:42:47 GMT

Hi,

You can use following script :

Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH

If this doesn't work, remove # after Date in above script.

Still if it gives error, Plz share sample data if you can.

Happy Thoughts

FORMATING DATE - HELP

Thu, 23 Sep 2010 10:07:14 GMT

Still no luck. I must be doing something wrong. This is what I am trying to run now

SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DateFormat='DD/MM/YYYY';
SQL SELECT `se_AP`,
`se_DATEIMPORT`,
`se_FUND`,
`se_FundNAV`,
`se_ID`,
`se_IMPORTID`,
`se_SwapMTM`,
`se_SwapMTMFundNAVPer`,
`se_SwapMTMNotionalPer`,
`se_SwapNotional`,
Year(se_DATEIMPORT)as TYEAR,
Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH

FROM `dbo_SwapExposure`;

FORMATING DATE - HELP

Thu, 23 Sep 2010 10:08:21 GMT

This is my error:

SQL Error:[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3)'.
SQL Scriptline:
SQL State:37000
SQL SELECT `se_AP`,
`se_DATEIMPORT`,
`se_FUND`,
`se_FundNAV`,
`se_ID`,
`se_IMPORTID`,
`se_SwapMTM`,
`se_SwapMTMFundNAVPer`,
`se_SwapMTMNotionalPer`,
`se_SwapNotional`,
Year(se_DATEIMPORT)as TYEAR,
Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH

FROM `dbo_SwapExposure`

FORMATING DATE - HELP

Thu, 23 Sep 2010 10:12:52 GMT

MonthName is a Qlikview function, not SQL

you have to load first in a table T1 from SQL query and then you can use T2 : LOad *, left(monthname(....) as Tmonth resident T1

you can make it in one load using

Load *, left(monthname(....) as Tmonth;

SQL ....FROM dbo_SwapExposure

regards

christian

FORMATING DATE - HELP

Thu, 23 Sep 2010 10:24:21 GMT

This is what I wrote and tried to load butit didn't work

ODBC CONNECT TO [MS Access Database;DBQ=G:\ETP_TRADING AND OPERATIONS\Daily internal reports\Reporting DB\IM Access Database\ASSENAGON.mdb];
LOAD `se_ID`,
`se_FUND`,
`se_AP`,
`se_SwapNotional`,
`se_FundNAV`,
`se_SwapMTM`,
`se_SwapMTMNotionalPer`,
`se_SwapMTMFundNAVPer`,
Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH,
`se_IMPORTID`;

SQL SELECT `se_ID`,
`se_FUND`,
`se_AP`,
`se_SwapNotional`,
`se_FundNAV`,
`se_SwapMTM`,
`se_SwapMTMNotionalPer`,
`se_SwapMTMFundNAVPer`,
`se_DATEIMPORT`,
`se_IMPORTID`
Year(TMONTH)as YEAR,
MONTH(TMONTH)as REPORTING_MONTH
FROM `dbo_SwapExposure`;

FORMATING DATE - HELP

Thu, 23 Sep 2010 10:25:49 GMT

Can you possiblily show me how to set up the script

FORMATING DATE - HELP

Thu, 23 Sep 2010 10:32:39 GMT

For me it looks like TMONTH is not a field from Database but a result of a Qlikview function

so try this

T1:

SQL SELECT `se_ID`,
`se_FUND`,
`se_AP`,
`se_SwapNotional`,
`se_FundNAV`,
`se_SwapMTM`,
`se_SwapMTMNotionalPer`,
`se_SwapMTMFundNAVPer`,
`se_DATEIMPORT`,
`se_IMPORTID`
FROM dbo

T2:

LOAD

Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH,
year(se_DATEIMPORT) as Tyear

resident T1

FORMATING DATE - HELP

Thu, 23 Sep 2010 10:40:15 GMT

Ok gave this a try

SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DateFormat='DD/MM/YYYY';

Table1:
SQL SELECT `se_AP`,
`se_DATEIMPORT`,
`se_FUND`,
`se_FundNAV`,
`se_ID`,
`se_IMPORTID`,
`se_SwapMTM`,
`se_SwapMTMFundNAVPer`,
`se_SwapMTMNotionalPer`,
`se_SwapNotional`
FROM `dbo_SwapExposure`;

Table2:
LOAD

*,
Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH,
year(se_DATEIMPORT) as Tyear

Resident table1

However an error has come up saying

Table not found
Table2:
LOAD

*,
Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH,
year(se_DATEIMPORT) as Tyear

Resident table1

FORMATING DATE - HELP

jonathandienst — Thu, 23 Sep 2010 10:56:15 GMT

The table names in Qlikview aere case sensitive.

Change the line saying Resident table1 to Resident Table1

Jonathan

FORMATING DATE - HELP

Thu, 23 Sep 2010 11:02:35 GMT

Ok... it has successfully loaded however, the TMONTH field is blank now

FORMATING DATE - HELP

Thu, 23 Sep 2010 17:15:52 GMT

Can someone help? Now I'm left with the new field being left blank.....

Not sure what else I can do

FORMATING DATE - HELP

Thu, 23 Sep 2010 17:29:34 GMT

Try simplifying the field just to get some data.

LOAD
*,
 Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH,
 Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')) as TMONTH1,
 Date#(se_DATEIMPORT,'DD-MMM-YY') as TMONTH2,
 se_DATEIMPORT as TMONTH3,
 year(se_DATEIMPORT) as Tyear

 Resident Table1

Do any of those return data?

FORMATING DATE - HELP

Thu, 23 Sep 2010 17:55:13 GMT

Thanks ! omg it works now....

and now i got another problem....

Basically it's a similar problem but I think my SQL is inefficient because it keeps crashing

I need to concatenate two tables then change the date format because it's returning numbers. But the problem is..... the query keeps crashing so I think what I have scripted is incorrect...

Example:

MasterTurnover:
SQL SELECT "msamk_id",
"msamk_ticker" as Key_Turnoverstatic,
"msamk_isin",
"msamk_dateimported",
"msamk_startdate" as TURNOVER_DATE,
"msamk_enddate",
"msamk_broker",
"msamk_currency",
"msamk_srcname",
"msamk_markitname",
"msamk_turnoverbroker",
"msamk_turnovertotal",
"msamk_brokerpercent",
"msamk_brokerrank",
"msamk_1st",
"msamk_2nd",
"msamk_3rd",
"SSMA_TimeStamp"
FROM msamarkit.dbo.msamarkit;

CONCATENATE ("MasterTurnover")

Turnoverbloomberg:
SQL SELECT "mastproav_id",
BBTicker as Key_Turnoverstatic,
"FUND_TOTAL_ASSETS",
"FUND_TOTAL_ASSETS_CRNCY",
"FUND_TOTAL_ASSETS_EUR",
"EQY_TURNOVER",
CRNCY,
"EQY_TURNOVER_EUR",
"PX_LAST",
"mastproav_Impid",
"mastproav_date" as TURNOVER_DATE,
"FUND_TOTAL_ASSETS_DT",
"FUND_NET_ASSET_VAL",
"NAV_CRNCY",
"FUND_NET_ASSET_VAL_EUR",
"FUND_NAV_DT",
"EQY_SH_OUT"
FROM AUM.dbo.masterprodaumvol;

Table1:
LOAD

*,
Left(Monthname(Date(TURNOVER_DATE,'DD/MM/YYYY')),3) as TMONTH,
year(TURNOVER_DATE) as Tyear

Resident MasterTurnover

FORMATING DATE - HELP

Thu, 23 Sep 2010 18:14:26 GMT

Concatanate is onyl really for tables which have the same fields (ala "union all"), i have a feeling you actually want to join the tables on the Key_Turnoverstatic and TURNOVER_DATE fields. Try:

MasterTurnover:
SQL SELECT "msamk_id",
"msamk_ticker" as Key_Turnoverstatic,
"msamk_isin",
"msamk_dateimported",
"msamk_startdate" as TURNOVER_DATE,
"msamk_enddate",
"msamk_broker",
"msamk_currency",
"msamk_srcname",
"msamk_markitname",
"msamk_turnoverbroker",
"msamk_turnovertotal",
"msamk_brokerpercent",
"msamk_brokerrank",
"msamk_1st",
"msamk_2nd",
"msamk_3rd",
"SSMA_TimeStamp"
FROM msamarkit.dbo.msamarkit;

left join (MasterTurnover)

SQL SELECT "mastproav_id",
BBTicker as Key_Turnoverstatic,
"FUND_TOTAL_ASSETS",
"FUND_TOTAL_ASSETS_CRNCY",
"FUND_TOTAL_ASSETS_EUR",
"EQY_TURNOVER",
CRNCY,
"EQY_TURNOVER_EUR",
"PX_LAST",
"mastproav_Impid",
"mastproav_date" as TURNOVER_DATE,
"FUND_TOTAL_ASSETS_DT",
"FUND_NET_ASSET_VAL",
"NAV_CRNCY",
"FUND_NET_ASSET_VAL_EUR",
"FUND_NAV_DT",
"EQY_SH_OUT"
FROM AUM.dbo.masterprodaumvol;

left join (MasterTurnover)

LOAD
Left(Monthname(Date(TURNOVER_DATE,'DD/MM/YYYY')),3) as TMONTH,
year(TURNOVER_DATE) as Tyear

Resident MasterTurnover

FORMATING DATE - HELP

Thu, 23 Sep 2010 18:38:11 GMT

I don't know if this makes a difference, but I know I did exactly what you're trying to do now before:

for the headers I have

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

I see you are using

SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DateFormat='DD/MM/YYYY';

maybe change

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

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

Then for your table load:

Load

se_DATEIMPORT, month(se_DATEIMPORT) as MDateImport,

Resident