Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
Please, anybody can help me?
I'm still struggling with it...
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
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
Try to concatenate both the fields and than use it in your application
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
Hi,
Can you please attach a sample, so that it would be easier to help you.
Regards,
Jagan.
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"
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].
Hello HRLinder,
Thanks for your answer.
Can I use function "IsNull" along with ApplyMap function?