topic Re: Linking 2 Tables to form 1 table in QlikView

Linking 2 Tables to form 1 table

Wed, 04 Nov 2015 19:36:11 GMT

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

Re: Linking 2 Tables to form 1 table

swuehl — Wed, 04 Nov 2015 19:49:58 GMT

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

Re: Linking 2 Tables to form 1 table

swuehl — Wed, 04 Nov 2015 19:52:20 GMT

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.

Re: Linking 2 Tables to form 1 table

Wed, 04 Nov 2015 20:13:47 GMT

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

Re: Linking 2 Tables to form 1 table

swuehl — Wed, 04 Nov 2015 20:28:21 GMT

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?

Re: Linking 2 Tables to form 1 table

arasantorule — Thu, 05 Nov 2015 02:45:14 GMT

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

Re: Linking 2 Tables to form 1 table

jagan — Thu, 05 Nov 2015 03:52:51 GMT

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.

Re: Linking 2 Tables to form 1 table

Thu, 05 Nov 2015 06:01:33 GMT

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

Re: Linking 2 Tables to form 1 table

jonathandienst — Thu, 05 Nov 2015 06:22:49 GMT

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

Re: Linking 2 Tables to form 1 table

jonathandienst — Thu, 05 Nov 2015 06:28:42 GMT

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)

Re: Linking 2 Tables to form 1 table

Thu, 05 Nov 2015 06:38:54 GMT

Hi Jonathan

Thank you for the feedback. I'm using the ApplyMap (the same that Sweuhl suggested) and see if it works.

Will let you know the outcome.

kind regards

Nayan

Re: Linking 2 Tables to form 1 table

Thu, 05 Nov 2015 07:41:44 GMT

Hi Jonathan

The figures are still doubling. Is it because its the same sales persons in both transactions table, or should this not have an effect?

kind regards

Nayan

Re: Linking 2 Tables to form 1 table

jagan — Thu, 05 Nov 2015 07:49:20 GMT

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.

Re: Linking 2 Tables to form 1 table

Thu, 05 Nov 2015 07:53:46 GMT

Hi Jagan

I'll check my links again on the App Map() . However i will try your method and let you know. Thank you for the feedback.

kind regards

Nayan

Re: Linking 2 Tables to form 1 table

Thu, 05 Nov 2015 10:48:46 GMT

Hi Jagan

Your scripting works perfectly. Also the App Mapp scripting worked as well. Thank you Jonathan & Swuehl for showing me App Mapp and will definitely be using this in the future.

kind regards

Nayan

Re: Linking 2 Tables to form 1 table

Thu, 05 Nov 2015 12:07:41 GMT

Hi All

I forgot to mention why the Apply(Map) did not work initially is a silly mistake on my side not having the proper joins in my original scripting. Otherwise it works perfectly.

kind regards

Nayan