FORMATING DATE - HELP

[#1]

Hi

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

[#2]

=

Hi thanks for respondong

What I am trying to do is an append query. MasterTurnover share some fieldsthe same fields

Normal 0 false false false EN-GB X-NONE X-NONE MicrosoftInternetExplorer4 Key_Turnoverstatic,

Normal 0 false false false EN-GB X-NONE X-NONE MicrosoftInternetExplorer4 TURNOVER_DATE

and Normal 0 false false false EN-GB X-NONE X-NONE MicrosoftInternetExplorer4 "msamk_turnoverbroker"= Normal 0 false false false EN-GB X-NONE X-NONE MicrosoftInternetExplorer4 "FUND_NET_ASSET_VAL_EUR",

[#3]

Why not use

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

Then use MonthName()

[munozdj1]

Hi,

I think you might want to do a preceding load for each of the SELECTs to modify the date on-the-fly before concatenating both tables.

Try this code:

MasterTurnover:
LOAD
*,
date(NewDate,'MMM') AS TMONTH,
year(NewDate) AS Tyear;
LOAD
*,
num(date#(TURNOVER_DATE,'DD-MM-YYYY')) AS NewDate;
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;

Turnoverbloomberg:
CONCATENATE (MasterTurnover)
LOAD
*,
date(NewDate,'MMM') AS TMONTH,
year(NewDate) AS Tyear;
LOAD
*,
num(date#(TURNOVER_DATE,'DD-MM-YYYY')) AS NewDate;
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;

DROP FIELD NewDate;

Tell me if that works for you, if not, I'll be happy to reply.