Concatenating tables.

[Markbhai]

I have two tables with very similar data and I want to combine them into one.

They have a unique key called 'data_sharing_id'

and similar fields such as dsa_exists and contract_exists will always show a value between them (one being completed the other being Null)

I want to create a combined table but my load script is not combining the non null values from the second table.

How do I get the whole table completed.

Load Script:

[data_sharing_agreement]:

LOAD

[data_sharing_id],

        [dsa_exsist],

Date([dsa_expiry] ) AS [dsa_expiry];

SELECT `data_sharing_id`,

`dsa_exsist`,

`dsa_expiry`,

`data_sharing_id`

FROM `db`.`data_sharing_agreement`;

CONCATENATE ([data_sharing_agreement]) LOAD

[contract_exsist] AS [dsa_exsist],

Timestamp([contract_expiry] ) AS [dsa_expiry];

SELECT `data_sharing_id`,

`contract_exsist`,

`contract_expiry`

FROM `db`.`contract_assessment`;

Thank you.

[igoralcantara]

The second table has a preceding load and that preceding load only has 2 columns, not all columns needed. You need to list all in the preceding load 

[TauseefKhan]

Hello @Markbhai 
To combine the two tables based on the data_sharing_id key and fill in the null values from the second table, you can use the JOIN or MATCH keyword in your load script.

I used the IsNull functions to fill in the null values from the second table.

[data_sharing_agreement]:
LOAD
[data_sharing_id],
[dsa_exsist],
Date([dsa_expiry] ) AS [dsa_expiry]
RESIDENT
[data_sharing_agreement]
AUTOGENERATE 1
;

[contract_assessment]:
LOAD
[data_sharing_id],
[contract_exsist] AS [dsa_exsist],
Timestamp([contract_expiry] ) AS [dsa_expiry]
RESIDENT
[contract_assessment]
AUTOGENERATE 1
;

[combined_table]:
LOAD
[data_sharing_id],
[dsa_exsist] AS [dsa_exists_agreement],
If(IsNull([dsa_exists_agreement]), [contract_exists], [dsa_exists_agreement]) AS [dsa_exists],
[dsa_expiry]
RESIDENT
[data_sharing_agreement]
MATCH
[data_sharing_id]
WITH
[contract_assessment]
;

**** When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to. ****

[Markbhai]

I was having some trouble with the above, but it was very helpful.  The issue I was having is that the 'AUTOGENERATE 1' and 'MATCH' were showing as Red Text in the Data Load editor and the script would not load.

I now have this:

[contract_assessment]:

LOAD

    [contract_exsist],

    [data_sharing_id];

SELECT 

`data_sharing_id`,

    `contract_exsist`

FROM `prove_privacy`.`contract_assessment`;

Concatenate ([contract_assessment])

LOAD DISTINCT

    [data_sharing_id],

    [dsa_exsist];

 SELECT 

`data_sharing_id`,

    `dsa_exsist`      

FROM `prove_privacy`.`data_sharing_agreement`;

[data_sharing_combined]:

LOAD DISTINCT

    [data_sharing_id],

    If(IsNull([dsa_exsist]), [contract_exsist], [dsa_exsist]) AS [exists]

RESIDENT [contract_assessment];

DROP TABLE [contract_assessment];

This creates the combined table, but I have duplicated data_sharing_id in the resulting table eg:

Data_sgaring_id, exists

1,  Null

1,  yes

2, Null

2,  yes

etc.

Can you offer more advice?

Thanks again

Mark.

[Markbhai · Accepted solution]

It turns out that the fields did not contain nulls, they contained either 'yes', 'no', or ' '.

I ended up concatenating the two tables using a select query where the contract_exsist field equalled either yes or no (and the same for the dsa_exists fields).

Full script below for those in a similar spot.

LOAD contract_description as [Description], 

id, 

contract_exsist as [Exists], 

contract_agreement, 

contract_format, 

contract_owner as [Owner], 

contract_name, 

contract_purpose, 

    Timestamp([contract_expiry] ) AS [Expiry],

contract_clauseA, 

contract_clauseB, 

contract_clauseC, 

contract_clauseD, 

contract_clauseE, 

contract_clauseF, 

contract_clauseG, 

contract_clauseH, 

contract_clauseI, 

contract_evidence, 

status as [Status], 

document_purpose, 

document_location, 

    if ([contract_exsist] = 'yes' or [contract_exsist] = 'no' , 'Contract') AS [Agreement];

[contract_assessment]:

SELECT `contract_description`,

id,

`contract_exsist`,

`contract_agreement`,

`contract_format`,

`contract_owner`,

`contract_name`,

`contract_purpose`,

`contract_expiry`,

`contract_clauseA`,

`contract_clauseB`,

`contract_clauseC`,

`contract_clauseD`,

`contract_clauseE`,

`contract_clauseF`,

`contract_clauseG`,

`contract_clauseH`,

`contract_clauseI`,

`contract_evidence`,

status,

`document_purpose`,

`document_location`

FROM `db`.`contract_assessment` WHERE `contract_exsist` = 'yes' or `contract_exsist` = 'no';

Concatenate ([contract_assessment])

LOAD DISTINCT

dsa_description as [Description], 

id, 

dsa_exsist as [Exists], 

dsa_owner as [Owner], 

    Timestamp([dsa_expiry] ) AS [Expiry],

dsa_self_assessment, 

storage_File, 

status as [Status], 

document_purpose, 

document_location, 

    if ([dsa_exsist] = 'yes' or [dsa_exsist] = 'no' , 'DSA') AS [Agreement];

[data_sharing_agreement]:

SELECT `dsa_description`,

id,

`dsa_exsist`,

`dsa_owner`,

`dsa_expiry`,

`dsa_self_assessment`,

`storage_File`,

status,

`document_purpose`,

`document_location`

FROM `db`.`data_sharing_agreement` WHERE `dsa_exsist` = 'yes' OR `dsa_exsist` = 'no';