Errors in Joining QVD Files

[asheppardwork]

How can I join multiple QVDs and specify the direction of the joins when the QVDs come from multiple sources and are created in the load script itself.

We are using Qlik Sense Feb 2022 edition running on Qlik Sense Server 14.54.2.  I have a data set that consists of two queries and two google sheets and one attached xlsx sheet all of which I am loading into qvds.  The ask is to link up old ids with new ids using a translation sheet that has fields of old_id, new_id, and old-new-id.   Qlik keeps 'helping' by making a $Syn1 table and key that is completely ruining the return data set by returning None of the old_id records match up with their new_id counterparts from the transfer sheet.  When I try to force the joins I get a 'field not found' error when it is clearly in the load scripts.  I cannot share the actual script so I've prepared some mock-ups in the thread.

Option 1 qvd loader:

Option 1: 

returning None of the old_id records match up with their new_id counterparts from the transfer sheet

//load query-a
 [query-a]:
 LOAD
   id_orig, 
   new_id,
    owner,
   item,
   transaction
 FROM [lib://My_Folder/query-a.qvd]
 (qvd);
 //load query-b
 [query-b]:
 NoConcatenate
 LOAD
    id_orig,
    new_id,
    owner,
    item,
    transaction
 FROM [lib://My_Folder/query-b.qvd]
 (qvd);
 //sheet-a qvd
 [sheet-a]:
 LOAD
    new_id,
    name
    
 FROM [lib://My_Folder/sheet-a.qvd]
 (qvd);
 //sheet-b qvd
 [sheet-b]:
 LOAD
    new_id,
   date
  
 FROM [lib://My_Folder/sheet-b.qvd]
 (qvd);
 //transfer_sheet qvd
 [transfer_sheet]:
 LOAD
    new_id,
    old_id,
  old-new-id
    
 FROM [lib://My_Folder/transfer_sheet.qvd]
 (qvd);

Option 2 qvd loader:

Option 2 produces an error like:

Results in fail to load error:
field new_id not found
near FROM [lib://My_Folder/sheet-b.qvd]

//sheet-a qvd
 [sheet-a]:
 LOAD
    new_id,
    name
    
 FROM [lib://My_Folder/sheet-a.qvd];
Inner Join 
 //sheet-b qvd
 [sheet-b]:
 LOAD
    new_id,
    date
  
 FROM [lib://My_Folder/sheet-b.qvd]
 (qvd);
Inner Join
//load query-a
 [query-a]:
 LOAD
   id_orig,
    new_id,
    owner,
   item,
   transaction
 FROM [lib://My_Folder/query-a.qvd];
Left Join
 //transfer_sheet qvd
 [transfer_sheet]:
 LOAD
    new_id,
    old_id,
    old-new-id
    
 FROM [lib://My_Folder/transfer_sheet.qvd]
 (qvd);
Left Join
 //load query-b
 [query-b]:
LOAD
   id_orig
    new_id,
    owner,
    item,
    transaction
 FROM [lib://My_Folder/query-b.qvd];

 Mockup of query A:

Query A:
[query-a]:
Load *,
PurgeChar(id_orig,'-.') as new_id;
SQL 
With a as (
( Select 
 a.usecase as id_orig,
 a.item
 b.owner
 c.transaction
   From table1 a
   Left Join table2 b
         on b.myItem = a.itemId
    Left Join table3 c
          on substring(a.usecase,4,10) = c.anotherId
     Where a.org = '3'
     AND upper (a.usecase) NOT LIKE '%ABC%'
)
UNION ALL
( Select 
 a.usecase as id_orig,
 a.item
 b.owner
 c.transaction
   From table1 a
   Left Join table2 b
         on b.myItem = a.itemId and upper (b.status) = 'WORKING'
    Left Join table3 c
          on substring(a.usecase,4,10) = c.anotherId)
     Where a.org = '3'
      AND upper (b.status) = 'WORKING'
      AND upper (a.usecase) NOT LIKE '%ABC%'
)
)
SELECT *
 ,now_number() over (
 partition by a.owner
 order by a.item desc
 ) ranker
From a
Order by ranker desc
;
store [query-a] into [lib://My_Folder/query-a.qvd];
drop table [query-a];

Mock up of Query B:

Query B:
[query-b]:
Load *,
PurgeChar( id_orig,'-.') as new_id;
SQL 
With a as (
( Select 
 a.usecase as id_orig,
 a.item
 b.owner
 c.transaction
   From table1 a
   Left Join table2 b
         on b.myItem = a.itemId
    Left Join table3 c
          on substring(a.usecase,4,10) = c.anotherId
     Where a.org = '3'
     AND split_part(upper(a.usecase),'-',1) = 'ABC'
)
UNION ALL
( Select 
 a.usecase as id_orig,
 a.item
 b.owner
 c.transaction
   From table1 a
   Left Join table2 b
         on b.myItem = a.itemId and upper (b.status) = 'WORKING'
    Left Join table3 c
          on substring(a.usecase,4,10) = c.anotherId)
     Where a.org = '3'
      AND upper (b.status) = 'WORKING'
      AND split_part(upper(a.usecase),'-',1) = 'ABC')
)
SELECT *
 ,now_number() over (
 partition by a.owner
 order by a.item desc
 ) ranker
From a
Order by ranker desc
;
store [query-b] into [lib://My_Folder/query-b.qvd];
drop table [query-b];

[rubenmarin · Accepted solution]

Hi, based on the arged model, doing a left join of both querys to trannsffer sheet ends with double fields for owner, item and transaction, so one of the table fields should be renamed.

The other option is to add rows from query A and Query B to be loaded you can do it as:

// LOAD query A data
Table:
LOAD * FROM TransferSheet;
LEft Join LOAd new_id, owner, item, transaction From QueryA;

// LOAD query B data in a different table to join with transfer and add the data to the previous table
tmpTableB:
NoConcatenate LOAD * FROM TransferSheet; 
LEft Join LOAd old_id, owner, item, transaction From QueryB;

Concatenate (Table) LOAD * Resident tmpTableB;
DROP Table tmpTAbleB;

// Add sheet data
Inner Join LOAD new_id, date FROM SheetA;
Inner Join LOAD new_id, name FROM SheetB;