Linking 2 Tables to form 1 table

[#1]

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

• the QV Model ,
• The data in  excel
• a Screen shot of the table viewer.  This is showing the 2 tables linked by the document number.  However I want to achieve only 1 table.

  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

[swuehl]

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);

[swuehl]

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.

[#4]

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

[swuehl]

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?

[arasantorule]

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

[jagan]

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.

[#8]

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

[jonathandienst]

>>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.

[jonathandienst]

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)