topic Re: Combining Name Fields in QlikView

Combining Name Fields

gfisch13 — Fri, 09 Jun 2017 22:12:02 GMT

Hi Everyone, I have First Name and Last Name in two separate fields and would like to combine them into FullName.

I have just tried this action and am receiving an error message, the name fields are the same data type, unsure why this happened???

Any help is appreciated. Is is the load statement from my script and I was trying to do the combination on the fly.

SQL##f - SqlState: 37000, ErrorCode: 402, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]The data types nvarchar and varchar are incompatible in the '&' operator.

SQL SELECT "ACCT_NUM",

"ACCT_STTS_CTGY_CD",

"AUDT_ID",

"CMDTY_CD",

"DATA_SRC_CD",

"EMPE_FRST_NM",

"EMPE_ID",

"EMPE_LST_NM",

"EMPE_FRST_NM" & ' ' & "EMPE_LST_NM" as "CitiFullName",

"ETL_CYC_DT",

"HRCH_LVL_1_CD",

"HRCH_LVL_1_NM",

"HRCH_LVL_2_CD",

"HRCH_LVL_2_NM",

"HRCH_LVL_3_CD",

"HRCH_LVL_3_NM",

"ITEM_DSCR",

"LN_ITEM_DSCR",

MCC,

"MCC_CTGY",

"MCC_DSCR",

"PRCH_HRCH_NM",

"TCKT_NUM",

"TRAN_AMT",

"TRAN_CD_GRP",

"TRAN_CUR_CD",

"TRAN_DB_CR_IND",

"TRAN_DT",

"TRAN_TYP_DSCR",

"VND_CITY_NM",

"VND_CNTRY_CD",

"VND_NM",

"VND_ST_CD",

"VND_ZIP_CD"

FROM "DMBA_SIU".dbo."T_CITICARD_CHRG_PRST"

Re: Combining Name Fields

zebhashmi — Sat, 10 Jun 2017 00:58:51 GMT

you are using different syntax

so try this

"EMPE_FRST_NM", "EMPE_LST_NM" as "CitiFullName",

"EMPE_FRST_NM"+"EMPE_LST_NM" as "CitiFullName",

Re: Combining Name Fields

Anil_Babu_Samineni — Sat, 10 Jun 2017 03:30:22 GMT

May be do this?

Load *,

"EMPE_FRST_NM" & ' ' & "EMPE_LST_NM" as "CitiFullName";

SQL SELECT "ACCT_NUM",

"ACCT_STTS_CTGY_CD",

"AUDT_ID",

"CMDTY_CD",

"DATA_SRC_CD",

"EMPE_FRST_NM",

"EMPE_ID",

"EMPE_LST_NM",

"ETL_CYC_DT",

"HRCH_LVL_1_CD",

"HRCH_LVL_1_NM",

"HRCH_LVL_2_CD",

"HRCH_LVL_2_NM",

"HRCH_LVL_3_CD",

"HRCH_LVL_3_NM",

"ITEM_DSCR",

"LN_ITEM_DSCR",

MCC,

"MCC_CTGY",

"MCC_DSCR",

"PRCH_HRCH_NM",

"TCKT_NUM",

"TRAN_AMT",

"TRAN_CD_GRP",

"TRAN_CUR_CD",

"TRAN_DB_CR_IND",

"TRAN_DT",

"TRAN_TYP_DSCR",

"VND_CITY_NM",

"VND_CNTRY_CD",

"VND_NM",

"VND_ST_CD",

"VND_ZIP_CD"

FROM "DMBA_SIU".dbo."T_CITICARD_CHRG_PRST";

Re: Combining Name Fields

sunny_talwar — Sat, 10 Jun 2017 10:15:47 GMT

Have you tried it like this? (https://www.techonthenet.com/sql_server/functions/concat.php)

CONCAT( string1, string2, ... string_n)

CONCAT("EMPE_FRST_NM", ' ', "EMPE_LST_NM") as "CitiFullName",

Or you can use + to concatenate them (+ (String Concatenation) (Transact-SQL) | Microsoft Docs)

"EMPE_FRST_NM" + ' ' + "EMPE_LST_NM" as "CitiFullName",

Re: Combining Name Fields

gfisch13 — Mon, 12 Jun 2017 16:52:38 GMT

Thank you for the response. I will look into using this function as there is utility beyond what I did here.

Re: Combining Name Fields

gfisch13 — Mon, 12 Jun 2017 16:53:32 GMT

Anil - I had tried this previously and I got an error message I think because those fields hadn't loaded yet. Thank you for your response!!!!