topic Re: Joining one dimension with two from another table in QlikView

Joining one dimension with two from another table

Anonymous — Mon, 23 Feb 2015 13:12:33 GMT

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

Re: Joining one dimension with two from another table

robert_mika — Mon, 23 Feb 2015 13:34:46 GMT

Could you post sample of your data?

Re: Joining one dimension with two from another table

Mon, 23 Feb 2015 13:40:10 GMT

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.

Re: Joining one dimension with two from another table

Anonymous — Mon, 23 Feb 2015 13:44:18 GMT

Hi,

Please create composite key as below and join tables:

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

Re: Joining one dimension with two from another table

sunny_talwar — Mon, 23 Feb 2015 14:13:59 GMT

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

Best,

Re: Joining one dimension with two from another table

awhitfield — Mon, 23 Feb 2015 14:23:20 GMT

Hi Colin,

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

Re: Joining one dimension with two from another table

Anonymous — Mon, 23 Feb 2015 14:31:20 GMT

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

Re: Joining one dimension with two from another table

Anonymous — Mon, 23 Feb 2015 15:44:04 GMT

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

Re: Joining one dimension with two from another table

linusblomberg — Mon, 23 Feb 2015 15:48:13 GMT

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?

Re: Joining one dimension with two from another table

Mon, 23 Feb 2015 15:57:53 GMT

Hi,

could you please attached a qvw

Re: Joining one dimension with two from another table

Anonymous — Mon, 23 Feb 2015 17:18:41 GMT

Producer_ID and Client_ID will always be the different. They are basically subsets of the larger list Client_Producer_ID.

I have attached a sample version of the file with the most recent attempt described above. I had previously tried to (left) join the tables as normal. Either way I'm getting a loop error or blank fields.

Thanks all for the help.

Re: Joining one dimension with two from another table

linusblomberg — Tue, 24 Feb 2015 08:04:02 GMT

Yes I know what you're doing wrong. First there is faulty where statement, you probably want where 'not' isnull():

Clients:

LOAD client_id AS [Client ID],

//producer_id,

orders

FROM

(ooxml, embedded labels, table is CLIENT_PRODUCER)

WHERE ISNULL(client_id);

------------------------------------------------

on the first and second tab (producer and client) you can use the left join there between the two load statements on each tab.

On the last tab I would use concatenate() instead of a left join.

Hope this helps

Re: Joining one dimension with two from another table

Anonymous — Tue, 24 Feb 2015 09:12:36 GMT

Thanks Linus.

As you said, that should have been either WHERE NOT ISNULL(client_id); or else WHERE ISNULL(producer_id)

I have left joined the statements on the first two tabs, but I'm not entirely clear on how the concatenate should be used in the last tab. Would it be possible for you to provide an example of the code?

Are the temp QVD files needed or should they be removed?

Re: Joining one dimension with two from another table

Anonymous — Tue, 24 Feb 2015 09:24:45 GMT

Hi Rakesh,

Attached is a sample file.

Thanks

Re: Joining one dimension with two from another table

linusblomberg — Tue, 24 Feb 2015 09:56:45 GMT

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;

Re: Joining one dimension with two from another table

Anonymous — Tue, 24 Feb 2015 11:12:38 GMT

Thanks Linus. This worked perfectly. Much appreciated.

Re: Joining one dimension with two from another table

linusblomberg — Tue, 24 Feb 2015 11:45:28 GMT

No worries, glad I could help...