Concatenate

[#1]

Trying to concatenate data from two tables.

Table1_ID	Table1_Letter
1	a
2	b
3	c

Table2_ID	Table2_Letter	Table2_Dum
4	d	aa
5	e	bb
6	f	cc
1	dd	DD

The result Im searching for is as follows

Table1_ID	Table1_Letter
1	a
2	b
3	c
4	d
5	e
6	f

This is the script I'm using.  I've tried so many different versions and just cant seem to get it right.  Appreciate your help.

Table1:

LOAD Table1_ID,

     Table1_Letter

FROM

(ooxml, embedded labels, table is Sheet1);

Table2Temp:

NoConcatenate

LOAD Table2_ID as Table1_ID,

     Table2_Letter as Table1_Letter,

     Table2_Dum

FROM

(ooxml, embedded labels, table is Sheet2);

Concatenate(Table1)

LOAD Table1_ID,

     Table1_Letter

//     Table2_Dum

Resident Table2Temp

where NOT EXISTS (Table1_ID);

[nagaiank]

Johannes,

Please see the post No. 4 above. In order to exclude one line from Table2, the where clause is needed.

Thanks.

[#3]

This is my actual code.  Have tried many variations and am now frustrated as cannot seem to find the solution.   The code runs - it just doesnt concatenate at the end.

LOADING THE INCOME TABLE

Income:

LOAD Company as BrokerCode,

     Branch,

     Company & '-' & Branch & '-' & Client_Number & '-' & Year(AddMonths(Invoice_Period,6)) &'-'& Month(Invoice_Period) &'-'& Risk &'-'& Income_Class as MapKey,

     Company & '-' & Branch & '-' & Client_Number & '-' & Year(AddMonths(Invoice_Period,6)) &'-'& Month(Invoice_Period) &'-'& Risk &'-'& Income_Class as BudgetMapKey,    

     Company & '-' & Branch & '-' & Client_Number & '-' & Year(AddMonths(Invoice_Period,6)) &'-'& Month(Invoice_Period) as MapKeyServicer,

     Servicer,

     Client_Number,

     Client_Name,

     Income_Class,

     Risk,

     Premium,

     Gross_Brokerage,

     Sub_Agent,

     Broker_Fees,

     Fees,

     Net_Income,

     Invoice_Period,

     Year(AddMonths(Invoice_Period,6)) as Fiscal_Year,

     Month(Invoice_Period) as I_Month,

     Cover_No,

     Risk_Class

FROM

(qvd);

CREATING MAP TO INSERT NET INCOME IN BUDGET TABLE

Income_Budget_Map_Net_Income:

Mapping

Load

       distinct(MapKey),

       Sum([Net_Income]) as Map_Net_Income

Resident Income

Group By MapKey;

LOADING BUDGET TABLE AND APPLYING MAP

Budget:

Load

       *,

       ApplyMap('Income_Budget_Map_Net_Income',BudgetMapKey,'Missing') as Budget_Net_Income;

LOAD CLB_Company,

     CLB_Branch,

     CLB_ClientNo,

     Month(makedate(left(CLB_Period,4),right(CLB_Period,2))) as Budget_Month,

     Year(AddMonths(makedate(left(CLB_Period,4),right(CLB_Period,2)),6)) as Budget_Fiscal_Year,

     CLB_Company & '-' & CLB_Branch & '-' & CLB_ClientNo & '-' & Year(AddMonths(makedate(left(CLB_Period,4),right(CLB_Period,2)),6)) &'-'& Month(makedate(left(CLB_Period,4),right(CLB_Period,2))) &'-'& CLB_RiskCode &'-'& CLB_IncomeClass as BudgetMapKey,

     CLB_RiskCode,

     CLB_IncomeClass,

     CLB_B_NET_INCOME

FROM

(qvd);

ATTEMPTING TO CONCATENATE INTO THE BUDGET TABLE THE DATA FROM THE INCOME TABLE WHERE THE “BUDGETMAPKEY” DOES NOT EXIST.

BudgetTemp1:

Load *

where not exists (BudgetMapKey);

LOAD BrokerCode as CLB_Company,

     Branch as CLB_Branch,

     Client_Number as CLB_ClientNo,

     I_Month as Budget_Month,

     Fiscal_Year as Budget_Fiscal_Year,

     BudgetMapKey,

     Risk as CLB_RiskCode,

     Income_Class as CLB_IncomeClass,

     '-' as CLB_B_NET_INCOME,

     Net_Income as Budget_Net_Income

Resident Income;

[nagaiank]

A modified script is given below. Please see it this works. As I do not have the qvd files, I was unable to test by actually running the script. The changes are highlighted in red.

//LOADING THE INCOME TABLE

Income:

LOAD Company as BrokerCode,

     Branch,

     Company & '-' & Branch & '-' & Client_Number & '-' & Year(AddMonths(Invoice_Period,6)) &'-'& Month(Invoice_Period) &'-'& Risk &'-'& Income_Class as MapKey,

//     Company & '-' & Branch & '-' & Client_Number & '-' & Year(AddMonths(Invoice_Period,6)) &'-'& Month(Invoice_Period) &'-'& Risk &'-'& Income_Class as BudgetMapKey,   

     Company & '-' & Branch & '-' & Client_Number & '-' & Year(AddMonths(Invoice_Period,6)) &'-'& Month(Invoice_Period) as MapKeyServicer,

     Servicer,

     Client_Number,

     Client_Name,

     Income_Class,

     Risk,

     Premium,

     Gross_Brokerage,

     Sub_Agent,

     Broker_Fees,

     Fees,

     Net_Income,

     Invoice_Period,

     Year(AddMonths(Invoice_Period,6)) as Fiscal_Year,

     Month(Invoice_Period) as I_Month,

     Cover_No,

     Risk_Class

FROM (qvd);

//CREATING MAP TO INSERT NET INCOME IN BUDGET TABLE

Income_Budget_Map_Net_Income:

Mapping

Load

       distinct(MapKey),

       Sum([Net_Income]) as Map_Net_Income

Resident Income

Group By MapKey;

//LOADING BUDGET TABLE AND APPLYING MAP

Budget:

Load

       *,

       ApplyMap('Income_Budget_Map_Net_Income',BudgetMapKey,'Missing') as Budget_Net_Income;

LOAD CLB_Company,

     CLB_Branch,

     CLB_ClientNo,

     Month(makedate(left(CLB_Period,4),right(CLB_Period,2))) as Budget_Month,

     Year(AddMonths(makedate(left(CLB_Period,4),right(CLB_Period,2)),6)) as Budget_Fiscal_Year,

     CLB_Company & '-' & CLB_Branch & '-' & CLB_ClientNo & '-' & Year(AddMonths(makedate(left(CLB_Period,4),right(CLB_Period,2)),6)) &'-'& Month(makedate(left(CLB_Period,4),right(CLB_Period,2))) &'-'& CLB_RiskCode &'-'& CLB_IncomeClass as BudgetMapKey,

     CLB_RiskCode,

     CLB_IncomeClass,

     CLB_B_NET_INCOME

FROM (qvd);

//ATTEMPTING TO CONCATENATE INTO THE BUDGET TABLE THE DATA FROM THE INCOME TABLE WHERE THE “BUDGETMAPKEY” DOES NOT EXIST.

//BudgetTemp1:

//Load *

//where not exists (BudgetMapKey);

Concatenate (Budget)

LOAD BrokerCode as CLB_Company,

     Branch as CLB_Branch,

     Client_Number as CLB_ClientNo,

     I_Month as Budget_Month,

     Fiscal_Year as Budget_Fiscal_Year,

     MapKey as BudgetMapKey,

     Risk as CLB_RiskCode,

     Income_Class as CLB_IncomeClass,

     '-' as CLB_B_NET_INCOME,

     Net_Income as Budget_Net_Income

Resident Income

where Not Exists(BudgetMapKey,MapKey);

[#5]

Table1:

LOAD Table1_ID,

     Table1_Letter

FROM

(ooxml, embedded labels, table is Sheet1);

Table2Temp:

NoConcatenate

LOAD Table2_ID as Table1_ID,

     Table2_Letter as Table1_Letter,

     Table2_Dum

FROM

(ooxml, embedded labels, table is Sheet2);

left join (Table2Temp)

load

Table1_ID,

     Table1_Letter,

     1 as FLG

resident Table1;

Concatenate(Table1)

LOAD Table1_ID,

     Table1_Letter

//     Table2_Dum

Resident Table2Temp

where isnull(FLG);