Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Can you put some Input and Output example, to make easier define a join?
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
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
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
Hi, indeed, but you need to join both fields? Can you supress one of then or change field name?
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;