topic Re: Concatenate and synthetic key created - why ? in App Development

Concatenate and synthetic key created - why ?

patricesalem — Tue, 05 Oct 2021 00:02:12 GMT

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

Re: Concatenate and synthetic key created - why ?

Digvijay_Singh — Tue, 05 Oct 2021 03:16:33 GMT

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

Re: Concatenate and synthetic key created - why ?

patricesalem — Tue, 05 Oct 2021 06:52:12 GMT

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 ?

Re: Concatenate and synthetic key created - why ?

Digvijay_Singh — Tue, 05 Oct 2021 12:01:29 GMT

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.

Re: Concatenate and synthetic key created - why ?

patricesalem — Tue, 05 Oct 2021 13:12:20 GMT

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

Re: Concatenate and synthetic key created - why ?

Digvijay_Singh — Tue, 05 Oct 2021 13:41:41 GMT

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.

Re: Concatenate and synthetic key created - why ?

patricesalem — Tue, 05 Oct 2021 14:14:20 GMT

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

Re: Concatenate and synthetic key created - why ?

Digvijay_Singh — Tue, 05 Oct 2021 14:16:54 GMT

Then you should use force concatenation using concatenate keyword..

Re: Concatenate and synthetic key created - why ?

patricesalem — Tue, 05 Oct 2021 14:26:44 GMT

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;

Re: Concatenate and synthetic key created - why ?

patricesalem — Tue, 05 Oct 2021 15:03:30 GMT

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

Re: Concatenate and synthetic key created - why ?

Digvijay_Singh — Tue, 05 Oct 2021 15:17:45 GMT

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.

Re: Concatenate and synthetic key created - why ?

patricesalem — Tue, 05 Oct 2021 19:51:08 GMT

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;