topic Problems Merging Fields in App Development

Problems Merging Fields

Markbhai — Tue, 28 May 2024 19:19:35 GMT

I am trying to populate a field in a joined table with a field in the first part of the join [org_id] - see the code in bold.

I get an error saying org_id not found. - I am sure this will be soomething really simple but my brain has stopped functioning.

[Organizations]:

LOAD

id as [org_id],

org_name,

isDeleted,

isSubsidiary

// if(isSubsidiary=0, id) as [main_id]

WHERE isDeleted = 0;

SELECT

id,

`org_name`,

isDeleted,

isSubsidiary,

isMainco

FROM `db`.organizations;

Left Join ([Organizations]) LOAD [sub_id] as org_id,

sub_id,

main_id,

if(ISNULL([main_id]), [org_id]) as NewField;

SELECT `sub_id`,

`main_id`

FROM `db`.subsidiary;

Any help appreciated,

Mark

Re: Problems Merging Fields

marksouzacosta — Tue, 28 May 2024 19:48:00 GMT

Replace org_id with sub_id. At that point in your Load Script, loading data from subsidiary, the field org_id does not exist.

Left Join ([Organizations]) LOAD [sub_id] as org_id, sub_id, main_id, if(ISNULL([main_id]), [sub_id]) as NewField ; SELECT `sub_id`, `main_id` FROM `db`.subsidiary ;

Re: Problems Merging Fields

Markbhai — Tue, 28 May 2024 20:11:37 GMT

The issue I have is that the organizations table contains all of the companies, which may be a parent or a subsidiary, but this table does not allow us to link a parent to a subsidiary.

The subsidiary table does the linking, but lists organisations which have parents and subsidiaries (i.e the companies witout a subsidiary are not listed in the subsidiary table.

Therefore if main_id is null, so is the sub_id

What I want to achieve is for the field currently lables as main_id in this table to be populated with the org_id when there is no entry in the subsidiary table. i.e. in the third line does it would contain 39

Thanks

Mark.

Re: Problems Merging Fields

Markbhai — Tue, 28 May 2024 20:27:01 GMT

It seems like I got there in the end:

[organizations]:

LOAD

id as [org_id],

org_name,

isDeleted,

isSubsidiary

WHERE isDeleted = 0;

SELECT

id,

`org_name`,

isDeleted,

isSubsidiary,

isMainco

FROM `db`.organizations;

Left Join([organizations])

//[subsidiary]:

LOAD [sub_id] as [org_id],

sub_id,

main_id,

;

SELECT `sub_id`,

`main_id`

FROM `db`.subsidiary;

TempTable_tmp:

Load

[org_id] as orgnew,

[sub_id] as subnew,

[main_id] as mainnew,

if(ISNULL([main_id]), [org_id], [main_id]) as NewField

Resident [organizations];

Thanks Marcus, it was your comment regarding the org_id not being present that made me consider a new table from the old one.