topic Can I combine two field in the same column? in QlikView

Can I combine two field in the same column?

Mon, 05 Dec 2011 16:55:15 GMT

Dear all,

I've built the following table containing two fields: "Branch_Number_Rpt" and "Branch_Code_Open"

The first two records derive from Table GGL.xlsx. They contain "Branch_Number_Rpt" field value, which is a derived mapping field,

whereas the rest records containing "Branch_Code_Number" field value derived from Table LOAN.xlsx. (Branch_Code_Number is from Table Customer.xlsx, the others fields are from Table LOAN.xlsx).

I'd like my above table report to combine both fields together ("Branch_Number_Rpt" and "Branch_Code_Open").

I tried to edit my LOAD Script like:

LOAD Cust_Number,

Firstname,

Lastname,

Address,

FI_Flag,

Branch_Code_Open AS Branch_Number_Rpt

FROM

[Cust Table.xlsx]

(ooxml, embedded labels, table is Sheet1);

However, it doesn't work.

How could I combine both fields together?

Hope anybody can help me. Thank you very much...

Can I combine two field in the same column?

Mon, 05 Dec 2011 19:48:34 GMT

Please, anybody can help me?

I'm still struggling with it...

Can I combine two field in the same column?

Mon, 05 Dec 2011 20:16:20 GMT

You could try to concat you fields and trim the result:

trim(Branch_Number_RPT&Branch_Code_Open) as NewFieldName,

Just replace the NewFieldName with your desired name.

Hope this gets you started

Can I combine two field in the same column?

Mon, 05 Dec 2011 20:22:32 GMT

Maybe try this: change in both table your variable by something like this

First table

Text( Num(Branch_Code_Open, '0000')) AS Branch_Number_Rpt

Second table

Text( Num(Branch_Number_Rpt, '0000')) as Branch_Number_Rpt

Can I combine two field in the same column?

kamalqlik — Tue, 06 Dec 2011 06:55:28 GMT

Try to concatenate both the fields and than use it in your application

Can I combine two field in the same column?

RSvebeck — Tue, 06 Dec 2011 07:13:25 GMT

Try this:

Fact:
load
Cust_Number,
Firstname,
Lastname,
Address,
FI_Flag,
Branch_Number_Rpt
FROM [LOAN.xlsx] (ooxml, embedded labels, table is Sheet1);

concatenate (Fact)
load
Cust_Number,
Firstname,
Lastname,
Address,
FI_Flag,
Branch_Code_Open AS Branch_Number_Rpt
FROM [GGL.xlsx] (ooxml, embedded labels, table is Sheet1);

Regards Robert

Can I combine two field in the same column?

jagan — Tue, 06 Dec 2011 07:57:38 GMT

Hi,

Can you please attach a sample, so that it would be easier to help you.

Regards,

Jagan.

Can I combine two field in the same column?

Anonymous — Tue, 06 Dec 2011 09:03:06 GMT

try this:

TEST:

LOAD Cust_Number,

Firstname,

Lastname,

Address,

FI_Flag,

Branch_Number_Rpt

FROM

[Cust Table.xlsx]

(ooxml, embedded labels, table is Sheet1)

where isnull(Branch_Code_Open);

CONCATENATE

LOAD Cust_Number,

Firstname,

Lastname,

Address,

FI_Flag,

Branch_Code_Open AS Branch_Number_Rpt

FROM

[Cust Table.xlsx]

(ooxml, embedded labels, table is Sheet1)

where isnull(Branch_Number_Rpt);

You might need to format both fields (eg like Text) but they seems to be identical. Maybe isnull doesnot work, so try "not IsNum"

Can I combine two field in the same column?

Tue, 06 Dec 2011 17:41:15 GMT

Dear all,

Thanks a lot for your solution. By the way, the problem is the field "Branch_Number_Rpt"

derives from mapping. I couldn't upload QV file as I'm using a free license, but here is my script.

-----------------------

BranchMap:

MAPPING

LOAD BR_Code_IOC_Code,

Branch_Number

FROM

branchMap.xlsx

(ooxml, embedded labels, table is Sheet1);

LOAD '024' AS Organization_Id,

'14034' AS BLS_Amount_Type,

'116002' AS Reporting_Group_Id,

GL_Code,

ApplyMap('BLS_GGLMap',GL_Code) as BLS_Item,

InsideOutside_Country_Indicator,

Trx_Currency_Code,

Book_Currency_Code,

Branch_Code,

Cost_Center_Code,

ApplyMap('BranchMap',Branch_Code&Cost_Center_Code&InsideOutside_Country_Indicator) AS Branch_Number_Rpt,

Class_Key,

PL_BS_Indicator,

GL_Code_Description,

Amount,

Load_Source,

Snapshot_Date

FROM

[GGL Table.xlsx]

(ooxml, embedded labels, table is Sheet1)

WHERE(Class_Key = 'A');

LOAD '024' AS Organization_Id,

'14034' AS BLS_Amount_Type,

'116002' AS Reporting_Group_Id,

Cust_Number,

Product_Code,

ApplyMap('BLS_LNSMap',Product_Code) as BLS_Item,

Amount_In_Country_Reporting,

Unearned_Amount,

Unrealized_Amount,

Amount_In_Country_Reporting - Unearned_Amount - Unrealized_Amount AS Amount,

Load_Source,

Snapshot_Date

FROM

[Loan Table.xlsx]

(ooxml, embedded labels, table is Sheet1);

LOAD Cust_Number,

Firstname,

Lastname,

Address,

FI_Flag,

Branch_Code_Open

FROM

[Cust Table.xlsx]

(ooxml, embedded labels, table is Sheet1);

-----------------------

Please note that the records in shown table come from 2 main table, [GGL Table.xlsx] and [Loan Table.xlsx].

Field Branch_Number_Rpt is a mapped field for [GGL Table.xlsx].

Field Branch_Code_Open is a joined field from [Cust Table.xlsx] for [Loan Table.xlsx].

Can I combine two field in the same column?

Tue, 06 Dec 2011 17:46:25 GMT

Hello HRLinder,

Thanks for your answer.

Can I use function "IsNull" along with ApplyMap function?