Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks - I'm hoping someone can advise me on what I'm doing wrong with the IF statement in my SQL load statement below (in Red). I'm trying to create a field that will populate with a Yes/No but I'm getting a syntax error. What is the proper format for this statement? Thanks!!
SQL SELECT //"AUDT_ID",
"CO_CD",
"BRTH_DT",
"CITY_NM",
"FRST_NM",
"ID",
"LST_NM",
"MAIL_ADR_TXT",
"MID_INIT_TXT",
"PHYS_LOC_TXT",
"SRV_DT",
"ST_TXT",
"STTS_CD",
"TRM_DT",
"ZIP_CD",
"MAIL_ADR_TXT" + ' ' + "ZIP_CD" as AddressMatch,
// "ETL_CYC_DT",
"DEPT_TXT",
If(Len(Trim("FRST_NM"))>0, 'Yes', 'No') as ["MatchInd"],
"JOB_TITL_TXT"
FROM "DMBA".dbo."EMPE_PRST";
statement you are using in SQL is Qlik native you can't use it in SQL statement. Try preceding load like below
LOAD *,
If(Len(Trim("FRST_NM"))>0, 'Yes', 'No') as ["MatchInd"];
SQL SELECT --"AUDT_ID",
"CO_CD",
"BRTH_DT",
"CITY_NM",
"FRST_NM",
"ID",
"LST_NM",
"MAIL_ADR_TXT",
"MID_INIT_TXT",
"PHYS_LOC_TXT",
"SRV_DT",
"ST_TXT",
"STTS_CD",
"TRM_DT",
"ZIP_CD",
"MAIL_ADR_TXT" + ' ' + "ZIP_CD" as AddressMatch,
-- "ETL_CYC_DT",
"DEPT_TXT",
"FRST_NM",
"JOB_TITL_TXT"
FROM "DMBA".dbo."EMPE_PRST";
Try a Case statement
Case
When Len(Trim(FRST_NM)) > 0 Then 'Yes'
Else 'No'
End as [MatchInd]
statement you are using in SQL is Qlik native you can't use it in SQL statement. Try preceding load like below
LOAD *,
If(Len(Trim("FRST_NM"))>0, 'Yes', 'No') as ["MatchInd"];
SQL SELECT --"AUDT_ID",
"CO_CD",
"BRTH_DT",
"CITY_NM",
"FRST_NM",
"ID",
"LST_NM",
"MAIL_ADR_TXT",
"MID_INIT_TXT",
"PHYS_LOC_TXT",
"SRV_DT",
"ST_TXT",
"STTS_CD",
"TRM_DT",
"ZIP_CD",
"MAIL_ADR_TXT" + ' ' + "ZIP_CD" as AddressMatch,
-- "ETL_CYC_DT",
"DEPT_TXT",
"FRST_NM",
"JOB_TITL_TXT"
FROM "DMBA".dbo."EMPE_PRST";
Hi Kush - thanks again for the input....while this appears to have worked I did notice that my new field only contains the value 'Yes' while I know there are situations where it should be 'No.'
Is there something further that this function needs?
On further review, the code works perfectly......Thank you Kush andJwjackso for your input!!