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]

Can you possiblily show me how to set up the script

[#3]

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

[#4]

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

[jonathandienst]

The table names in Qlikview aere case sensitive.

Change the line saying Resident table1 to Resident Table1

Jonathan

[#6]

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

[#7]

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

Not sure what else I can do

[#8]

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?

[#9]

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

[#10]

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

[#11]

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

to

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