Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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 ?
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.
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
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.
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
Then you should use force concatenation using concatenate keyword..
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;
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