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]

Can you share data model image? Haven't seen in detail but this section looks like is written in the loop and for each iteration it looks like creating new table that might cause synthetic key - 

LATE_DELIVERIES_TEMP:
  NoConcatenate LOAD 
    '$(currentId)' as key_temp,
    '$(currentOrder)' as COMMANDE_tem

[patricesalem]

Hello Digvijay_Singh

It looks like you might have found the issue...it takes ages to create the data model, here is a screenshot after 5min of process :

How could I fix the issue in the loop ?

[Digvijay_Singh]

I don't know the overall requirements but any reason you are using Noconcatenate in the load inside loop, if you remove that, automatically you will have just a single table with all the data concatenated.

[patricesalem]

no reason...it was just one of my trials....

I've tried using the following code and removing the third section (the one that was concatenating) tables :

[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)


LATE_DELIVERIES_TEMP:
  LOAD 
    '$(currentId)' as key,
    '$(currentOrder)' as COMMANDE,
    '$(currentLine)' as POSTE_temp,
    '$(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;
Drop Table LATE_DELIVERIES_TEMP;

No data is added to SALES. I thought that using the same field names in LATE_DELIVERIES_TEMP than SALES, the inline data would automatically concatenated into SALES. It's not the case...What am I doing wrong ?

THANKS

[Digvijay_Singh]

As long as all the fieldnames are exactly same with no extra or less field count, and no explicit 'noconcatenate' keyword, the tables must be concatenated, may be you need to check the script again, also check each and every line of the log window, sometimes it gives the indication of the location its doing something wrong.

[patricesalem]

Thanks

I have less fields in the "loop created table". In another qvf, I can manage to concatenate two tables that don't have the same number of fields....how can I do with my current approach ?

thanks

[Digvijay_Singh]

Then you should use force concatenation using concatenate keyword..

[patricesalem]

I have tried the following with no luck :

Concatenate [SALES]:
//LATE_DELIVERIES_TEMP:
  LOAD 
    '$(currentId)' as key,
    '$(currentOrder)' as COMMANDE,
    '$(currentLine)' as POSTE_temp,
    '$(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,
    'TOTO' 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;
//Drop Table LATE_DELIVERIES_TEMP;
drop table ORDER_Shipped;

[patricesalem]

I did find !

[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:
  LOAD 
    '$(currentId)' as key,
    '$(currentOrder)' as COMMANDE,
    '$(currentLine)' as POSTE_temp,
    '$(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,
    'TOTO' 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;

In the next section :

[SALES] :
load key,COMMANDE,Nb_Weeks_Late
resident [SALES_TEMP];
drop table [SALES_TEMP];

Concatenate [SALES]:
Load key,COMMANDE,Nb_Weeks_Late
resident [LATE_DELIVERIES_TEMP];

Drop Table LATE_DELIVERIES_TEMP;

When I try the following (to avoid capturing all fields from Sales_Temp), it does not work. Why do you think ?

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

Concatenate [SALES]:
Load key,COMMANDE,Nb_Weeks_Late
resident [LATE_DELIVERIES_TEMP];

Drop Table LATE_DELIVERIES_TEMP;

THANKS A LOT