Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have 2 tables , a, Transactions table and a Sales Persons table , as shown below. The common field between the 2 tables is the Document number.
I am uncertain how to merge the 2 tables so that in my table viewer i want to see 1 table.
I have attached
Please can you help.
Thank you .
Transactions Table | ||
Document | Customer | Value |
1 | A | 200 |
2 | B | 500 |
3 | C | 300 |
4 | D | 200 |
5 | E | 300 |
Sales Person Table | |
Document | Sales Person |
1 | Dave |
2 | John |
3 | Mike |
4 | Dave |
5 | Gary |
Hi Nayan,
By using ApplyMap() your data will not be doubled the other way is try like this
Transactions:
LOAD *
From Transactions1;
Concatenate(Transactions) -- Except SalesPerson load all columns
LOAD ...
DocumentNo
...
FROM Transactions2;
SalesPerson:
LOAD DocumentNo, SalesPerson
FROM Salesperson;
Concatenate(SalesPerson)
LOAD DISTINCT
DocumentNo,
SalesPerson
FROM Transactions2;
Hope this helps you.
Regards,
jagan.
If you don't want to link the two tables as you've done in your sample, you can JOIN them in the script.
Just add a JOIN LOAD prefix in your script:
Sales_Person:
LEFT JOIN (Transactions)
LOAD Document,
[Sales Person]
FROM
[Join Test.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
Alternatively, you can look into
Don't join - use Applymap instead
Especially if your Document - Salesperson relation is not 1:1, a join will duplicate facts.
Thanks Swuehl. Will Try Applymap when I get to work tomorrow and let you know the outcome.
I am familiar with left join in your first response which I use often.
kind regards
Nayan
Also the applymap will not really handle the scenario when more than one salesperson can be linked to a document.
Could this be the case?
Hi,
Please see the below. Also separate both the tables in to different sheets
Transactions:
LOAD Document,
Customer,
Value
FROM
[Join Test.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
Sales_Person:
LOAD Document,
[Sales Person]
FROM
[Join Test.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
Left Join(Transactions)
LOAD Document,[Sales Person]
Resident Sales_Person;
drop Table Sales_Person;
Thanks
Hi,
Why you are trying to join two tables into one? You can maintain it separately instead without any issues, sometimes join may lead to data duplication if there ie many to many mapping. Is there any specific reason to join this two tables?
Regards,
Jagan.
Hi Jagan
The reason why i ask for this request is that I have the following situation. The first being the one above where its a Transaction table (and a separate Sales Persons table with a document number as a common link.
I also have another Transactions table that already has a sales persons field in it.
So initially I done a left join of the Sales Person table to the Transactions table. Thereafter i concatenated the second transactions table to the first transactions table.
The final results that i was getting , was that my values were doubling & tripling.
kind regards
Nayan
>>The final results that i was getting , was that my values were doubling & tripling.
Concatenate the transaction tables together, but leave the sales person as a separate table as Jagan suggested. The sales person has multiple values linking to the transactions and the join is creating multiple records, resulting in doubling, tripling etc.
Otherwise simplify the sales person table to ensure that the document number is unique (only one record per document number) in the sales person table. If you can do that, you can use the join.
I re-read your post and replies, and I think I understand better. In your case I would create a mapping table and use ApplyMap to bring the sales person into the first transaction table.
Map_SalesPerson:
Mapping LOAD DocumentNo, SalesPerson
FROM Salesperson;
Transactions:
LOAD ...
DocumentNo,
ApplyMap('Map_SalesPerson', DocumentNo, 'Unknown') As SalesPerson,
...
From Transactions1;
Concatenate(Transactions)
LOAD ...
DocumentNo,
SalesPerson
...
FROM Transactions2;
(Change the table/field names and complete the remaining code to suit)