Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Linking 2 Tables to form 1 table

 

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
DocumentCustomerValue
1A200
2B500
3C300
4D200
5E300

 

Sales Person Table
DocumentSales Person
1Dave
2John
3Mike
4Dave
5Gary
1 Solution

Accepted Solutions
MVP
MVP

Re: Linking 2 Tables to form 1 table

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.


15 Replies
MVP
MVP

Re: Linking 2 Tables to form 1 table

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

MVP
MVP

Re: Linking 2 Tables to form 1 table

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.

Not applicable

Re: Linking 2 Tables to form 1 table

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

MVP
MVP

Re: Linking 2 Tables to form 1 table

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
Contributor III

Re: Linking 2 Tables to form 1 table

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

MVP
MVP

Re: Linking 2 Tables to form 1 table

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.

Not applicable

Re: Linking 2 Tables to form 1 table

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

MVP
MVP

Re: Linking 2 Tables to form 1 table

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Linking 2 Tables to form 1 table

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser