Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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`;
Can you possiblily show me how to set up the script
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
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
The table names in Qlikview aere case sensitive.
Change the line saying Resident table1 to Resident Table1
Jonathan
Ok... it has successfully loaded however, the TMONTH field is blank now
Can someone help? Now I'm left with the new field being left blank.....
Not sure what else I can do
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?
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
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
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