Solved: Joining one dimension with two from another table - Qlik Community

Anonymous · ‎2015-02-23

Hi,

I am having a problem joining two tables based on one particular dimension which needs to be linked to two separate dimensions.

The first table contains a field called Client_Producer_ID. This contains a unique ID for each client and producer which are actually two different fields. It also has a field indicating the total number of Orders with that client or producer.

The second table has four fields: Client ID which contains IDs found in Client_Producer_ID.

Producer ID which also contains IDs found in the master list Client_Producer_ID.

Company Type which is a number identifier as to whether the company is a client or producer.

Company Name which has the name of the client or producer.

The ideal output would be something like this:

Client_Producer_ID	Company Name	Company Type	# Orders
312678	ABC	Producer	26
513456	XYZ	Client	42
876775	MNO	Client	15
543379	JKI	Producer	18

Or:

Client ID	Producer ID	Company Name	Company Type	# Orders
-	312678	ABC	Producer	26
513456	-	XYZ	Client	42
876775	-	MNO	Client	15
-	543379	JKI	Producer	18

I have tried both mapping and joins but have not been able to get the tables to link properly. What I have done is try to define Client_Producer_ID as either Company_ID or Producer_ID, based on the value of Company Type in the second table. However, I continue to get null values entirely for either one of these fields depending on which may I join or map.

Any ideas on the best way to do this?

Thanks

linusblomberg · ‎2015-02-24

Here: There is no need to create QVDs in this scenario. Use a resident Load. Also, you don't need a where statement when you left join because only the matches will be joined anyway (given that both tables doesn't contain lots of blank rows.

Anyway. I would do this (and then you can store Fact as a QVD if you need it in another app:

Producers:

LOAD //client_id,

producer_id AS [Producer ID],

orders

FROM

(ooxml, embedded labels, table is CLIENT_PRODUCER)

WHERE not ISNULL(producer_id);

left join

LOAD client_producer_id AS [Producer ID],

company_type,

company_name

FROM

(ooxml, embedded labels, table is COMPANY);

Clients:

LOAD client_id AS [Client ID],

//producer_id,

orders

FROM

(ooxml, embedded labels, table is CLIENT_PRODUCER)

WHERE not ISNULL(client_id);

left join

LOAD client_producer_id AS [Client ID],

company_type,

company_name

FROM

(ooxml, embedded labels, table is COMPANY);

Fact:

load [Producer ID] as ClientProducerID,

orders,

company_type,

company_name

Resident Producers;

Concatenate(Fact)

load [Client ID] as ClientProducerID,

orders,

company_type,

company_name

Resident Producers;

DROP TABLEs Clients, Producers;

View solution in original post

robert_mika · ‎2015-02-23

Could you post sample of your data?

Report Inappropriate Content · ‎2015-02-23

What type of join did you try? You should be able to left join the second table onto the first table and it will bring over the fields for any records in the second table that match on the field Client_Producer_ID in the first table. You just need to rename that field in the second table as Client_Producer_ID when doing the left join so it merges with the other ID field in the first table.

Anonymous · ‎2015-02-23

Hi,

Please create composite key as below and join tables:

load *,
Text(Client_ID&'-'&Producer_ID) as Client_Producer_ID
FROM User.qvd

sunny_talwar · ‎2015-02-23

Is it always going to be the case that you will either have Client ID or Producer ID given to you, never both?

Best,

S

awhitfield · ‎2015-02-23

Hi Colin,

have to got an example qvw OR sample of the data you are loading, which you can post?

Anonymous · ‎2015-02-23

Hi,

Are you sure that when you have a Client ID, you don't have the Producer ID ?

If so, you can simple do:

LOAD

...

if(len([Client ID])>0,[Client ID],[Producer ID]) as Client_Producer_ID

Then the two tables will be associated with the dimension Client_Producer_ID

Anonymous · ‎2015-02-23

Hi Robert,

Thanks for your reply.

Sure. I realise I have actually described the table layouts above incorrectly. The data should make it clear however as to how it's structured.

The last solution I had tried was to create a temporary QVD file with both Producer ID and Client ID defined and then load these back in.

See below.

Producers:

LOAD

Producer_ID AS [Producer ID],

// Client_ID

Orders AS [# Orders]

FROM Producer_Client.xml

WHERE ISNULL(Client_ID);

LOAD
client_producer_ID,
company_type,
company_name

FROM Company.xml

WHERE company_type=Producer;

STORE Producers INTO Qlikview\Temp Files\Producers.qvd (QVD);

DROP TABLE Producers

// New Tab

Clients:

LOAD

//Producer_ID

Client_ID AS [Client ID],

Orders AS [# Orders]

FROM Producer_Client.xml

WHERE ISNULL(Producer_ID);

LOAD
client_producer_ID,
company_type,
company_name

FROM Company.xml

WHERE company_type=Client;

STORE Clients INTO Qlikview\Temp Files\Clients.qvd (QVD);

DROP TABLE Clients

// New Tab

Table:

LOAD

[Client ID],

[# Orders]

FROM

[Qlikview\Temp Files\Clients.qvd] (QVD);

LEFT JOIN (Table)

LOAD

[Producer ID]

[# Orders]

FROM

[Qlikview\Temp Files\Producers.qvd] (QVD);

linusblomberg · ‎2015-02-23

You need to specify your input better, the output will be self explanatory. It's always dangerous when merging two IDs into one. Can ProducerID and ClientID have the same value? Do you have anything in the first table that specifies if it is a Client or Producer?

Report Inappropriate Content · ‎2015-02-23

Hi,

could you please attached a qvw