Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi folks,
I'm trying to convert a DATETIME field to DATE. The field I'm loading comes from and SQL Source using an SQL SELECT format in my script.
I've read several posts on this site and used a function that I thought would work but when I reload I'm getting an error that states 'date' is not a recognized SQL function. I'm open to learning any new ways to accomplish this task! Script Below:
VenMaster:
SQL SELECT "ACT_REC_IND",
"ADR_SEQ_NUM",
// "AUDT_ID",
"BNK_SET_A_ID",
"DATA_SRC_CD",
"EFF_DT",
Date(Date(Left("EFF_DT",10),'MM/DD/YYYY')) as Date,
// "ETL_CYC_DT",
// "ETL_UPDT_DT",
// "ETL_XTRCT_DT",
"VND_ADR_1_TXT",
"VND_ADR_2_TXT",
"VND_ADR_3_TXT",
"VND_ADR_4_TXT",
"VND_CITY_NM",
"VND_CLASS_CD",
"VND_DBA_NM",
"VND_ID",
"VND_NM",
"VND_PST_CD",
"VND_ADR_1_TXT" + ' ' + "VND_PST_CD" as AddressMatch,
"VND_ST_CD",
"VND_STTS_CD"
FROM "DMBA_SIU".dbo."T_APRV_VND_DTL_PRST";
Try to use a preceding load. This way you get your data through SQL and can transform it with QlikView-Syntax and there's no need to use database specific SQL.
VenMaster:
LOAD
*,
Date(Floor(EFF_DT) AS EFF_Date
;
SQL SELECT "ACT_REC_IND",
"ADR_SEQ_NUM",
// "AUDT_ID",
"BNK_SET_A_ID",
"DATA_SRC_CD",
"EFF_DT",
// "ETL_CYC_DT",
// "ETL_UPDT_DT",
// "ETL_XTRCT_DT",
"VND_ADR_1_TXT",
"VND_ADR_2_TXT",
"VND_ADR_3_TXT",
"VND_ADR_4_TXT",
"VND_CITY_NM",
"VND_CLASS_CD",
"VND_DBA_NM",
"VND_ID",
"VND_NM",
"VND_PST_CD",
"VND_ADR_1_TXT" + ' ' + "VND_PST_CD" as AddressMatch,
"VND_ST_CD",
"VND_STTS_CD"
FROM "DMBA_SIU".dbo."T_APRV_VND_DTL_PRST";
Explanation of the transformation (if EFF_DAT is a timestamp field)
Floor(...) => get rid of the hours, minutes,..., else you have in a listbox the date multiple times
Date(...) => convert into a QlikView-Date
Kind regards
Peter
Try to use a preceding load. This way you get your data through SQL and can transform it with QlikView-Syntax and there's no need to use database specific SQL.
VenMaster:
LOAD
*,
Date(Floor(EFF_DT) AS EFF_Date
;
SQL SELECT "ACT_REC_IND",
"ADR_SEQ_NUM",
// "AUDT_ID",
"BNK_SET_A_ID",
"DATA_SRC_CD",
"EFF_DT",
// "ETL_CYC_DT",
// "ETL_UPDT_DT",
// "ETL_XTRCT_DT",
"VND_ADR_1_TXT",
"VND_ADR_2_TXT",
"VND_ADR_3_TXT",
"VND_ADR_4_TXT",
"VND_CITY_NM",
"VND_CLASS_CD",
"VND_DBA_NM",
"VND_ID",
"VND_NM",
"VND_PST_CD",
"VND_ADR_1_TXT" + ' ' + "VND_PST_CD" as AddressMatch,
"VND_ST_CD",
"VND_STTS_CD"
FROM "DMBA_SIU".dbo."T_APRV_VND_DTL_PRST";
Explanation of the transformation (if EFF_DAT is a timestamp field)
Floor(...) => get rid of the hours, minutes,..., else you have in a listbox the date multiple times
Date(...) => convert into a QlikView-Date
Kind regards
Peter
You are using Qlik native functions in SQL statement which will not work. You need to use functions which is supported by your source i.w native to Sql source. You can try something like below
VenMaster:
SQL SELECT "ACT_REC_IND",
"ADR_SEQ_NUM",
// "AUDT_ID",
"BNK_SET_A_ID",
"DATA_SRC_CD",
"EFF_DT",
CONVERT("EFF_DT", getdate(), 101) as DATE,
// "ETL_CYC_DT",
// "ETL_UPDT_DT",
// "ETL_XTRCT_DT",
"VND_ADR_1_TXT",
"VND_ADR_2_TXT",
"VND_ADR_3_TXT",
"VND_ADR_4_TXT",
"VND_CITY_NM",
"VND_CLASS_CD",
"VND_DBA_NM",
"VND_ID",
"VND_NM",
"VND_PST_CD",
"VND_ADR_1_TXT" + ' ' + "VND_PST_CD" as AddressMatch,
"VND_ST_CD",
"VND_STTS_CD"
FROM "DMBA_SIU".dbo."T_APRV_VND_DTL_PRST";
The preceding load worked better in this situation as my weakness with SQL did not allow me to address some errors that appeared.
Errors were mainly syntax related.
Thank you both for your contributions and learning experience.