Concatenate and synthetic key created - why ?

[patricesalem]

Hello

I'm loading 2 tables in two different load sections. I then concatenate both in a third section. After hours trying eveything : qualify/unqualify, noconcatenate, field renaming...I can't get rid of the syn key automatically created...I can't even understand why a syn key is created for a concatenation load.

First section:

LIB CONNECT TO 'XXX';
LOAD
	text (COMMANDE)&'_'&num(POSTE) as key,
  	[COMMANDE]
    ,[ORIGINE]
	,[POSTE]
	,TYPE_COMMANDE as TYPE_cde
    ,[SECTEUR] as [Sector]
    ,[BUSINESS_UNIT] as [Business unit]
     , year (DATE_MAD)& num(week(DATE_MAD),'00') as YEAR_WEEK_REF_TAUX_SERVICE
[SALES_Temp]:
SELECT "ORIGINE"
	,"COMMANDE"
	,"POSTE"
	,"NET_POSTE"
	,"QTE_POSTE"
	,"SECTEUR"
	,"BUSINESS_UNIT"
    ,"TYPE_COMMANDE"
   ,"DATE_MAD"
  
    
FROM "QLIK"."ZSQ042" WHERE date_creation_poste>'01/07/2021' and QTE_POSTE>0 and TYPE_COMMANDE='TA';
//FROM "QLIK"."ZSQ042_TEST"; 
;

Second section

[ZSQ042_TEMP]:
LOAD
	key //commande & poste
    ,[COMMANDE]
    ,[POSTE]
    , YEAR_WEEK_REF_TAUX_SERVICE
    , WEEK_SM_REELLE_ZSQ
    
    ,[ORIGINE]
    ,[Business unit]
    ,[Sector]
    ,TYPE_cde
    
 resident [SALES_Temp]
 where num(WEEK_SM_REELLE_ZSQ) > num(YEAR_WEEK_REF_TAUX_SERVICE)
 ;
 
 
//on duplique toutes les lignes qui ne sont pas livrées à la semaine annoncée
 
 TempTable:
Load
  distinct
  key & '^' & COMMANDE & '^' & POSTE & '^' & YEAR_WEEK_REF_TAUX_SERVICE & '^' & WEEK_SM_REELLE_ZSQ & '^' &
  [ORIGINE] & '^' & [Business unit] & '^' & [Sector] & '^' & TYPE_cde
  as Id_Weeks
Resident
  [ZSQ042_TEMP]
;


// for each record in Id_Dates field
for i = 2 to FieldValueCount('Id_Weeks')
 
 // get the current iteration value
  let value = FieldValue('Id_Weeks', $(i));
  
  // extract the Id
  let currentId = SubField('$(value)', '^', 1);
  
  //Extract Order Number
  let currentOrder = SubField('$(value)', '^', 2);
  
  //Extract Order Line Number
  let currentLine = SubField('$(value)', '^', 3);
    
  // extract week to be delivered  
  let currentWeekStart = Num(SubField('$(value)', '^', 4));
  
  // extract week of delivery
  let currentWeekEnd = Num(SubField('$(value)', '^', 5));
  
  // extract origin
  let currentOrigin = SubField('$(value)', '^', 6);
  
  // extract BU
  let currentBU = SubField('$(value)', '^', 7);

// extract Sector
  let currentSector = SubField('$(value)', '^', 8);
  
  // extract Type de commande
  let currentType = SubField('$(value)', '^', 9);
   
// autogenerate all weeks between the week to deliver and week of delivery
   // add the current Id value (this will link to the RawData table)


LATE_DELIVERIES_TEMP:
  NoConcatenate LOAD 
    '$(currentId)' as key_temp,
    '$(currentOrder)' as COMMANDE_temp,
    '$(currentLine)' as POSTE_temp,
    '$(currentWeekStart)' as INITIAL_YEAR_WEEK_REF_TAUX_SERVICE_temp,
    $(currentWeekStart) + IterNo() as YEAR_WEEK_REF_TAUX_SERVICE_temp, //IterNo()-1
    '$(currentWeekEnd)' as WEEK_SM_REELLE_ZSQ_temp,
    IterNo() as Nb_Weeks_Late_temp,
    
    
    '$(currentOrigin)' as ORIGINE_temp,
    '$(currentBU)' as Business_unit_temp,
    '$(currentSector)' as Sector_temp,
    '$(currentType)' as TYPE_cde_temp
    
    
    
  AUTOGENERATE (1)
  WHILE 
    $(currentWeekStart) + IterNo() <= $(currentWeekEnd) //IterNo()-1
  ;

next

// we dont need this table anymore
Drop Table TempTable;
Drop Table ZSQ042_TEMP;

Third section

[SALES] :
load * resident [SALES_Temp];
drop table [SALES_Temp];

Concatenate [SALES]:
Load 
key_temp as key,
COMMANDE_temp as COMMANDE,
POSTE_temp as POSTE,
INITIAL_YEAR_WEEK_REF_TAUX_SERVICE_temp as INITIAL_YEAR_WEEK_REF_TAUX_SERVICE,
YEAR_WEEK_REF_TAUX_SERVICE_temp as YEAR_WEEK_REF_TAUX_SERVICE,
WEEK_SM_REELLE_ZSQ_temp as WEEK_SM_REELLE_ZSQ,
Nb_Weeks_Late_temp as Nb_Weeks_Late,
ORIGINE_temp as ORIGINE,
Business_unit_temp as Business_unit,
Sector_temp as Sector,
TYPE_cde_temp as TYPE_cde
Resident LATE_DELIVERIES_TEMP;
Drop Table LATE_DELIVERIES_TEMP;

Result :

Any idea what I'm doing wrong ?

thanks

[Digvijay_Singh · Accepted solution]

Not sure how this statement Concatenate [Sales]: is processed. You don't need to use table identifier [Sales]: as the next load is going to concatenate. Do you want to write Concatenate([Sales]) instead? that makes sense to me.

[patricesalem]

Thanks a lot for your help ! I managed to get it work as simple as possible, just using the right syntax as you've indicated in your last post !

[ZSQ042_TEMP]:
LOAD
	key //commande & poste
    ,[COMMANDE]
    ,[POSTE]
    , YEAR_WEEK_REF_TAUX_SERVICE
    , WEEK_SM_REELLE_ZSQ
    
    ,[ORIGINE]
    ,[Business unit]
    ,[Sector]
    ,TYPE_cde
    
 resident [SALES]
 where num(WEEK_SM_REELLE_ZSQ) > num(YEAR_WEEK_REF_TAUX_SERVICE)
 ;
 
 
//on duplique toutes les lignes qui ne sont pas livrées à la semaine annoncée
 
 TempTable:
Load
  distinct
  key & '^' & COMMANDE & '^' & POSTE & '^' & YEAR_WEEK_REF_TAUX_SERVICE & '^' & WEEK_SM_REELLE_ZSQ & '^' &
  [ORIGINE] & '^' & [Business unit] & '^' & [Sector] & '^' & TYPE_cde
  as Id_Weeks
Resident
  [ZSQ042_TEMP]
;


// for each record in Id_Dates field
for i = 2 to FieldValueCount('Id_Weeks')
 
 // get the current iteration value
  let value = FieldValue('Id_Weeks', $(i));
  
  // extract the Id
  let currentId = SubField('$(value)', '^', 1);
  
  //Extract Order Number
  let currentOrder = SubField('$(value)', '^', 2);
  
  //Extract Order Line Number
  let currentLine = SubField('$(value)', '^', 3);
    
  // extract week to be delivered  
  let currentWeekStart = Num(SubField('$(value)', '^', 4));
  
  // extract week of delivery
  let currentWeekEnd = Num(SubField('$(value)', '^', 5));
  
  // extract origin
  let currentOrigin = SubField('$(value)', '^', 6);
  
  // extract BU
  let currentBU = SubField('$(value)', '^', 7);

// extract Sector
  let currentSector = SubField('$(value)', '^', 8);
  
  // extract Type de commande
  let currentType = SubField('$(value)', '^', 9);
   
// autogenerate all weeks between the week to deliver and week of delivery
   // add the current Id value (this will link to the RawData table)


Concatenate([SALES])
LOAD 
    '$(currentId)' as key,
    '$(currentOrder)' as COMMANDE,
    '$(currentLine)' as POSTE,
    '$(currentWeekStart)' as INITIAL_YEAR_WEEK_REF_TAUX_SERVICE,
    $(currentWeekStart) + IterNo() as YEAR_WEEK_REF_TAUX_SERVICE, //IterNo()-1
    '$(currentWeekEnd)' as WEEK_SM_REELLE_ZSQ,
    IterNo() as Nb_Weeks_Late,
   
    
    
    '$(currentOrigin)' as ORIGINE,
    '$(currentBU)' as Business_unit,
    '$(currentSector)' as Sector,
    '$(currentType)' as TYPE_cde
    
    
    
  AUTOGENERATE (1)
  WHILE 
    $(currentWeekStart) + IterNo() <= $(currentWeekEnd) //IterNo()-1
  ;

next

// we dont need this table anymore
Drop Table TempTable;
Drop Table ZSQ042_TEMP;