topic combine to fields in QlikView

combine to fields

Thu, 28 Jul 2016 17:08:03 GMT

I need to combine two fields Last Name, First Name?

[PhrLName],	[PhrFName],
Winkel	Bob
Smith	John
To be
Winkel, Bob
Smith, John

LOAD [PhrNPI],

[PhrLName],

[PhrFName],

SQL SELECT "PhrNPI",

"PhrLName",

"PhrFName",

Re: combine to fields

Clever_Anjos — Thu, 28 Jul 2016 17:11:58 GMT

[PhrLName]&' ' & [PhrFName] as FullName

Re: combine to fields

vinieme12 — Thu, 28 Jul 2016 17:12:31 GMT

Fname&', '&Lname as fullname

Re: combine to fields

trdandamudi — Thu, 28 Jul 2016 17:16:18 GMT

In the script you can do as below:

Data:

Load

Lastname &',' &Firstname as Name;

LOAD * Inline [

Lastname,Firstname

Winkel,Bob

Smith,John

];

Re: combine to fields

sudeepkm — Thu, 28 Jul 2016 17:25:23 GMT

LOAD [PhrNPI],

[PhrFName]&' '&[PhrLName] as PhrFullName,

[PhrLName],

[PhrFName],

SQL SELECT "PhrNPI",

"PhrLName",

"PhrFName",

Re: combine to fields

gfisch13 — Fri, 09 Jun 2017 20:56:53 GMT

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

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: combine to fields

sudeepkm — Mon, 12 Jun 2017 19:08:56 GMT

You may use a preceding load on top of the SQL query and in the preceding load you can use your logic to create the new field.

LOAD

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

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