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