$Syn 1 how to remove

[43918084]

I try to join 4 tables using a "primkey".   2 of the tables are for last year (LY) and the other 2 are for Current quarter (CQ).  I expect after loading, the related primkey can compare LY vs CQ on the same row. But somehow after running the script, below synthetic key was generated and the LY and CQ of same primkey are separated into 2 rows.  

$Syn 1 = primkey+On/Off+ALN+Remarks 1+Ip Shrt Name Ith+Grca Account+Detail Product Code+Glbl Detail Customer Type Code+NACE+LY_stage+Grca Primary Account+LY_GCV_HKD000+CQ_stage+CQ_GCV_HKD000

Please find below the scripts of the 4 tables.  If I run Table 1 and Table 3 only, or Table 2 and Table4 only, there is no problem.  But if I run all 4 together, the $Syn issue happens.

Hope I could get some advice.  Many thanks.

//Table 1

LOAD
autonumberhash128( 'adj',"GRCA Account","Detail Product","Detail Customer Type","Remarks 2") as primkey,
'ON' as "On/Off",
'adj' as ALN,
"Remarks 1",
"Remarks 2"as "Ip Shrt Name Ith",
"GRCA Account" as "Grca Account",
"Detail Product" as "Detail Product Code",
"Detail Customer Type" as "Glbl Detail Customer Type Code",
"Custom 3" as NACE,
"Custom 6"as LY_stage,
"GRCA Primary Account" as "Grca Primary Account",
"Total"/1000 as LY_GCV_HKD000
FROM [lib://QS_Dev/AI80010_6 - Industry and Stage Analysis - Dec'20.xlsx]
(ooxml, embedded labels, header is 2 lines, table is [QS adj])
;

//Table 2

Outer join

LOAD
autonumberhash128( 'adj', "GRCA Account","Detail Product","Detail Customer Type","Remarks 2") as primkey,
'ON' as "On/Off",
'adj' as ALN,
"Remarks 1",
"Remarks 2"as "Ip Shrt Name Ith",
"GRCA Account" as "Grca Account",
"Detail Product" as "Detail Product Code",
"Detail Customer Type" as "Glbl Detail Customer Type Code",
"Custom 3" as NACE,
"Custom 6"as LY_stage,
"Custom 5" as "LY_CRR",
"GRCA Primary Account" as "Grca Primary Account",
"Total"/1000 as LY_GCV_HKD000
FROM [lib://QS_Dev/AI80010_1- CRREL Breakdown with Industry and Stage - Dec'20.xlsx]
(ooxml, embedded labels, header is 3 lines, table is [QS adj]);

//Table 3

outer Join
LOAD
autonumberhash128( 'adj',"GRCA Account","Detail Product","Detail Customer Type","Remarks 2") as primkey,
'ON' as "On/Off",
'adj' as ALN,
"Remarks 1",
"Remarks 2"as "Ip Shrt Name Ith",
"GRCA Account" as "Grca Account",
"Detail Product" as "Detail Product Code",
"Detail Customer Type" as "Glbl Detail Customer Type Code",
"New Custom 3" as NACE,
"New Custom 6"as CQ_stage,
"GRCA Primary Account" as "Grca Primary Account",
"Total"/1000 as CQ_GCV_HKD000
FROM [lib://QS_Dev/AI80010_6 - Industry and Stage Analysis -CQ.xlsx]
(ooxml, embedded labels, header is 3 lines, table is [QS Adj])
where "Adj type"='notbal';

//Table 4

outer Join
LOAD
autonumberhash128( 'adj',
"GRCA Account" ,"Detail Product","Detail Customer Type","Remarks 2") as primkey,
'ON' as "On/Off",
'adj' as ALN,
"Remarks 1",
"Remarks 2"as "Ip Shrt Name Ith",
"GRCA Account" as "Grca Account",
"Detail Product" as "Detail Product Code",
"Detail Customer Type" as "Glbl Detail Customer Type Code",
"New Custom 3" as NACE,
"New Custom 6"as CQ_stage,
"New Custom 5"as "CQ_CRR",
"GRCA Primary Account" as "Grca Primary Account",
"Total"/1000 as CQ_GCV_HKD000
FROM [lib://QS_Dev/AI80010_1 adj CQ.xlsx]
(ooxml, embedded labels, header is 2 lines, table is [QS adj])
where "C3 Change"='Changed' and "C5 Change"='Changed' and "C6 Change"='Changed';

[Vegar · Accepted solution]

By the looks og your data I believe  that Join might not be the best approach.  I would consider concatenating  the four sources into one table. Like this.

Tables:

//Table 1

LOAD

autonumberhash128( 'adj',"GRCA Account","Detail Product","Detail Customer Type","Remarks 2") as primkey,

'ON' as "On/Off",

'adj' as ALN,

"Remarks 1",

"Remarks 2"as "Ip Shrt Name Ith",

"GRCA Account" as "Grca Account",

"Detail Product" as "Detail Product Code",

"Detail Customer Type" as "Glbl Detail Customer Type Code",

"Custom 3" as NACE,

"Custom 6"as LY_stage,

"GRCA Primary Account" as "Grca Primary Account",

"Total"/1000 as LY_GCV_HKD000

FROM [lib://QS_Dev/AI80010_6 - Industry and Stage Analysis - Dec'20.xlsx]

(ooxml, embedded labels, header is 2 lines, table is [QS adj])

;

//Table 2

concatenate (Tables) 

LOAD

autonumberhash128( 'adj', "GRCA Account","Detail Product","Detail Customer Type","Remarks 2") as primkey,

'ON' as "On/Off",

'adj' as ALN,

"Remarks 1",

"Remarks 2"as "Ip Shrt Name Ith",

"GRCA Account" as "Grca Account",

"Detail Product" as "Detail Product Code",

"Detail Customer Type" as "Glbl Detail Customer Type Code",

"Custom 3" as NACE,

"Custom 6"as LY_stage,

"Custom 5" as "LY_CRR",

"GRCA Primary Account" as "Grca Primary Account",

"Total"/1000 as LY_GCV_HKD000

FROM [lib://QS_Dev/AI80010_1- CRREL Breakdown with Industry and Stage - Dec'20.xlsx]

(ooxml, embedded labels, header is 3 lines, table is [QS adj]);

//Table 3

concatenate (Tables)

LOAD

autonumberhash128( 'adj',"GRCA Account","Detail Product","Detail Customer Type","Remarks 2") as primkey,

'ON' as "On/Off",

'adj' as ALN,

"Remarks 1",

"Remarks 2"as "Ip Shrt Name Ith",

"GRCA Account" as "Grca Account",

"Detail Product" as "Detail Product Code",

"Detail Customer Type" as "Glbl Detail Customer Type Code",

"New Custom 3" as NACE,

"New Custom 6"as CQ_stage,

"GRCA Primary Account" as "Grca Primary Account",

"Total"/1000 as CQ_GCV_HKD000

FROM [lib://QS_Dev/AI80010_6 - Industry and Stage Analysis -CQ.xlsx]

(ooxml, embedded labels, header is 3 lines, table is [QS Adj])

where "Adj type"='notbal';

//Table 4

concatenate (Tables)

LOAD

autonumberhash128( 'adj',

"GRCA Account" ,"Detail Product","Detail Customer Type","Remarks 2") as primkey,

'ON' as "On/Off",

'adj' as ALN,

"Remarks 1",

"Remarks 2"as "Ip Shrt Name Ith",

"GRCA Account" as "Grca Account",

"Detail Product" as "Detail Product Code",

"Detail Customer Type" as "Glbl Detail Customer Type Code",

"New Custom 3" as NACE,

"New Custom 6"as CQ_stage,

"New Custom 5"as "CQ_CRR",

"GRCA Primary Account" as "Grca Primary Account",

"Total"/1000 as CQ_GCV_HKD000

FROM [lib://QS_Dev/AI80010_1 adj CQ.xlsx]

(ooxml, embedded labels, header is 2 lines, table is [QS adj])

where "C3 Change"='Changed' and "C5 Change"='Changed' and "C6 Change"='Changed';

[43918084]

Thanks a million for the guidance.  It works great now 🙂