Joining multiple tables

[Anonymous]

Hello Community,

Please help with the following:

I have 3 tables as follows:

Sales1:

Month

CustomerID

CustomerType

Revenue

Sales2:

Month

CustomerAccountNo

CustomerType

Revenue

As observed, the above two transactional tables contain revenue figures per month per customer, but have different customer identifiers (CustomerID is Sales1 and CustomerAccountNo in Sales2). The Month values in Sales1 and Sales2 are same, just that the CustomerType values are different.

In addition, I have two state tables that describe the customers:

Customers:

CustomerID

CustomerAccountNo

CustomerType

CustomerAddress

Age

SpecialCustomers:

CustomerID

CustomerAccountNo

CustomerGroup

Data sets are incomplete, so:

a. Customers table does not contain all Customer info (some SpecialCustomers are not mapped in Customers table)

b. Some Customers in Sales1 and Sales2 are not mapped in Customers

c. All Customers in Customers are not present in Sales1 and Sales2

How do I join (concatenate or link) these tables to have 1 or more associated tables so that I can have filters by CustomerType, Age, CustomerGroup, <other fields>, and present Revenue reports.

I have nulls for certain fields so will have to use set NullValue as well in the script, else few charts will show expression totals against nulls. (Example: Revenue by Age: for cases where a CustomerID is Sales1 but not in Customers will show a number against a Null age)

Thanks for the help.

Cheers

A

[eduardo_dimperio]

Can you put some Input and Output example, to make easier define a join?

[Anonymous]

HI Eduardo,

I have enclosed sample data set below:

Sales1:
Month	CustomerID	CustomerType	Revenue
Jan	ID1	Retail1	10
Jan	ID2	Retail1	20
Jan	ID3	Retail1	320
Jan	ID4	Retail1	400
Sales2:
Month	CustomerAccountNo	CustomerType	Revenue
Jan	AC1	Retail2	50
Jan	AC2	Retail2	60
Jan	AC3	Retail2	70
Jan	AC4	Retail2	80
Customers:
CustomerID	CustomerAccountNo	CustomerType	CustomerAddress	Age
ID1	AC10	Retail1	ABC	21
ID2	AC20	Retail1	DEF	26
ID3	AC30	Retail1	GHI	28
ID4	AC1	Retail2	JK	27
ID5	AC2	Retail2	MN	24
ID6	AC3	Retail2	OP	22
ID7	AC7	Retail1	EE	25
ID8	AC8	Retail2	RR	28
SpecialCustomers:
CustomerID	CustomerAccountNo	CustomerGroup
ID1	AC10	G1
ID4	AC1	G1
ID90	AC90	G2
ID100	AC100	G2

As you can observe, the table data is not all inclusive.

The output could be in one of the following ways:

a. A combined table "Sales" with Sales1 and Sales2 data, containing all fields from both tables. Nulls could be marked with <No Value>.

b. A combined table "AllCustomers" with Customers and SpecialCustomers data, identifying the group of the customer. Nulls could be marked with <No Value>.

c. Associative link between Sales and AllCustomers table.

The process may involve intermediate steps and that works for me.

Please advise.

Thanks

[eduardo_dimperio]

Hi Amit,

Try This

Sales:

Load

*

Resident Sales1

Concatenate (Sales)

Load

*

Resident Sales2

//*******************************************************************

Left Join(Sales)

Load

*

Resident Customers

Left Join(Sales)

Load

*

Resident SpecialCustomers

//*******************************************************************

NoConcatenate

Sales_Table:

LOAD

CustomerID,

CustomerType,

Revenue,

Month,

CustomerAccountNo,

IF(ISNULL(CustomerGroup),'No Value',CustomerGroup) AS CustomerGroup,

IF(ISNULL(CustomerAddress),'No Value',CustomerAddress) AS CustomerAddress,

IF(ISNULL(Age),'No Value',Age) AS Age,

RESIDENT Sales;

Drop table Sales

[Anonymous]

Hi Eduardo,

Thank you for the code snippets, but there is a problem with the joins -

After concatenating Sales1 and Sales2, the Sales table has both CustomerID and CustomerAccNo fields. Now when joining with Customers table, both fields are used to join, resulting in no common values. Hence, I get No Value in the final table.

Cheers

A

[eduardo_dimperio]

Hi, indeed, but you need to join both fields? Can you supress one of then or change field name?

[sumanta12]

MAPPING_TAB1:

MAPPING LOAD

CustomerID&'-'&CustomerType AS CUST_ID_TYPE,

    CustomerAccountNo

FROM

(ooxml, embedded labels, table is CUSTOMERS);

MAPPING_TAB2:

MAPPING LOAD

CustomerAccountNo&'-'&CustomerType AS CUST_ACCNO_TYPE,

CustomerID

FROM

(ooxml, embedded labels, table is CUSTOMERS);

===================================================================

TMP_SALES:

LOAD Month,

     CustomerID AS CUST_ID,

     CustomerType AS CUST_TYPE,

     APPLYMAP('MAPPING_TAB1',CustomerID&'-'&CustomerType,'') AS CUST_ACC_NO,

     Revenue

FROM

(ooxml, embedded labels, table is SALES1);

CONCATENATE(TMP_SALES)

LOAD Month,

     CustomerAccountNo AS CUST_ACC_NO,

     CustomerType AS CUST_TYPE,

     APPLYMAP('MAPPING_TAB2',CustomerAccountNo&'-'&CustomerType,'') AS CUST_ID,

     Revenue

FROM

(ooxml, embedded labels, table is SALES2);

NOCONCATENATE

SALES:

LOAD *,

CUST_ID&'-'&CUST_ACC_NO&'-'&CUST_TYPE AS CUST_ID_ACCNO_TYPE

RESIDENT TMP_SALES;

DROP TABLE TMP_SALES;

==============================================================================

TMP_CUSTOMER:

LOAD CustomerID&'-'&CustomerAccountNo AS CUST_ID_ACCNO,

CustomerID,

CustomerAccountNo,

     CustomerType,

     CustomerAddress,

     Age

FROM

(ooxml, embedded labels, table is CUSTOMERS);

JOIN(TMP_CUSTOMER)

LOAD CustomerID&'-'&CustomerAccountNo AS CUST_ID_ACCNO,

     CustomerGroup

FROM

(ooxml, embedded labels, table is [SPECIAL CUSTOMERS]);

NOCONCATENATE

CUSTOMER:

LOAD *,

CustomerID&'-'&CustomerAccountNo&'-'&CustomerType AS CUST_ID_ACCNO_TYPE

RESIDENT TMP_CUSTOMER;

DROP TABLE TMP_CUSTOMER;