Problems Merging Fields

[Markbhai]

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

[marksouzacosta]

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
;

[Markbhai]

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.

[Markbhai · Accepted solution]

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.