Can I combine two field in the same column?

[#1]

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...

[#2]

Please, anybody can help me?

I'm still struggling with it...

[#3]

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

[#4]

Hi

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

JJ

[kamalqlik]

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

[RSvebeck]

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

[jagan]

Hi,

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

Regards,

Jagan.

[Anonymous]

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"

[#9]

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].

[#10]

Hello HRLinder,

Thanks for your answer.

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