Joining/Link Scenario of 2 tables-How?

[dmohanty]

Hi All,

I have two Fact tables (2 excel data) to be loaded into QlikView.

Table 1 fields: Business Unit, Customer,Employee, Geography, Quarter (Q1,Q2,Q3,Q4), Total Revenue (in Mn.).

Table 2 fields:  Business Unit, Customer, Q1 Revenue,Q2 Revenue,Q3 Revenue (in Mn.),Q4 Revenue, Q1 Booking,Q2 Booking,Q3 Booking,Q4 Booking.

Any suggestions, how to load these 2 tables: to Join or Keep tables separate?

Also, Table 2 seems like a Cross Table as we have different Quarters as fields. Can we make them into a single column 'Quarter' like in Table 1?

Regards!

[alexandros17]

I would do nothing (but this depends on your cloud) and let the tables associate in qlikview natural way.

This will generate a syntethic key.

The second table can be loaded as crosstable if you need

[dmohanty]

Yes, clearly it would generate a Synthetic Key.

What would be more feasible way to load? What would be the Key here?

[marcus_malinow]

Ok a couple of issues:

To unravel your crosstable:

A simple way would be something like this:

Table2:

LOAD

Business Unit,

Customer,

'Q1' as Quarter

Q1 Revenue as Revenue,

Q1 Booking as Booking

FROM Table2

CONCATENATE (Table2)

LOAD

Business Unit,

Customer,

'Q2' as Quarter

Q2 Revenue as Revenue,

Q2 Booking as Booking

FROM Table2

etc..

To Link your tables:

Prior to your Table1 and Table2 loads use QUALIFY, e.g.

QUALIFY *;

UNQUALIFY '%*';

Then Add in unique key fields to each of your tables, this could be as simple as adding a RowNo().

e.g.

RowNo() as %Table1 //for the table 1 key.

Then build your link table:

LINK:

LOAD

%Table1,

Table1.Business Unit as Business Unit,

Table1.Customer as Customer,

Table1.Employee as Employee,

Table1.Quarter as Quarter

RESIDENT

Table1;

CONCATENATE (LINK)

LOAD

%Table2,

Table2.Business Unit as Business Unit,

Table2.Customer as Customer,

Table2.Quarter as Quarter

RESIDENT

Table2;