topic Re: Concatenating tables. in App Development

Concatenating tables.

Markbhai — Thu, 16 May 2024 20:29:00 GMT

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.

Re: Concatenating tables.

igoralcantara — Thu, 16 May 2024 21:05:06 GMT

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

Re: Concatenating tables.

TauseefKhan — Fri, 17 May 2024 05:12:17 GMT

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. ****

Re: Concatenating tables.

Markbhai — Fri, 17 May 2024 10:38:52 GMT

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.

Re: Concatenating tables.

Markbhai — Fri, 17 May 2024 12:59:55 GMT

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