Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I loaded two tables in another table and I need to add a column of auto increment, I used a rowno () function and thar did not work
Did you try with RecNo() or you can take a final Resident Load of the Assurance table to create a RowNo()
[FINAL_ASSURANCE]:
LOAD *,
RowNo() as New_tassurance_id
Resident ASSURANCE;
DROP Table ASSURANCE;
RENAME Table FINAL_ASSURANCE as ASSURANCE;
May be try RecNo() if you have Preceding Load on top of the table where you used RowNo()?
how did you load the tables? can you post the script?
UnQualify *;
[ASSURANCE]:
LOAD
rowno() as tassurance_id,
'PROJET_AVENIR' AS PRODUIT,
STAUT_CLIENT AS STATUT_CLIENT,
MARCHE,ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATION,
%ID_AGENCE_CTOS_CLIENT AS AGENCE,
AGE_RELATION,
DESCRIPTION_CATEGORIE_CLIENT,
%ID_CATEGORIE_CLIENT,
'TND' AS DEVISE,
'PROJET_AVENIR' AS NATURE,
'PRODUITS_FINANCIER' AS TYPE_DESC,
'ASSSURANCE' AS SOUS_TYPE_DESC,
'PROD_FIN' AS TYPE,
'ASS' AS SOUS_TYPE,
PROD_PA AS PROD_MOIS,
SEGMENT,
0 AS ATTRITION,
0 AS CONQUETE, DESCRIPTION_MARCHE_CLIENT,
STATUT_PA AS STATUT_PROD,
ANCIENNETE_RELATION,
NOMBRE_PA AS STOCK_NOMBRE,
0 AS PRODUCTION_NOMBRE,
0 AS PRODUCTTION_VOLUME_TND,
0 AS PRODUCTTION_VOLUME_DEV,
ENCOURS_PA AS STOCK_VOLUME_DEV,
ENCOURS_PA AS STOCK_VOLUME_TND,
SOUS_MARCHE,
TYPE_SOUS_MARCHE,
SEGMENT_2015 as SEGMENT_N_1
FROM
C:\pfe\DONNEES_RECAP\PA.qvd
(qvd);
LOAD
rowno() as tassurance_id,
'FAMILIA' AS PRODUIT,
STAUT_CLIENT AS STATUT_CLIENT,
MARCHE,ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATION,
%ID_AGENCE_CTOS_CLIENT AS AGENCE,
AGE_RELATION,
DESCRIPTION_CATEGORIE_CLIENT,
%ID_CATEGORIE_CLIENT,
'TND' AS DEVISE,
'FAMILIA' AS NATURE,
'PRODUITS_FINANCIER' AS TYPE_DESC,
'ASSSURANCE' AS SOUS_TYPE_DESC,
'PROD_FIN' AS TYPE,
'ASS' AS SOUS_TYPE,
PROD_FAMILIA AS PROD_MOIS,
SEGMENT,
0 AS ATTRITION,
0 AS CONQUETE, DESCRIPTION_MARCHE_CLIENT,
STATUT_FAMILIA AS STATUT_PROD,
ANCIENNETE_RELATION,
NOMBRE_FAMILIA AS STOCK_NOMBRE,
0 AS PRODUCTION_NOMBRE,
0 AS PRODUCTTION_VOLUME_TND,
0 AS PRODUCTTION_VOLUME_DEV,
0 AS STOCK_VOLUME_DEV,
0 AS STOCK_VOLUME_TND,
SOUS_MARCHE,
TYPE_SOUS_MARCHE,
SEGMENT_2015 as SEGMENT_N_1
FROM
C:\pfe\DONNEES_RECAP\FAMILIA.qvd
(qvd);
LOAD
rowno() as tassurance_id,
'BENOUN' AS PRODUIT,
STAUT_CLIENT AS STATUT_CLIENT,
MARCHE,ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATION,
%ID_AGENCE_CTOS_CLIENT AS AGENCE,
AGE_RELATION,
DESCRIPTION_CATEGORIE_CLIENT,
%ID_CATEGORIE_CLIENT,
'TND' AS DEVISE,
'BENOUN' AS NATURE,
'PRODUITS_FINANCIER' AS TYPE_DESC,
'ASSSURANCE' AS SOUS_TYPE_DESC,
'PROD_FIN' AS TYPE,
'ASS' AS SOUS_TYPE,
PROD_BENOUN AS PROD_MOIS,
SEGMENT,
CONQUETE,
0 AS ATTRITION, DESCRIPTION_MARCHE_CLIENT,
STATUT_BENOUN AS STATUT_PROD,
ANCIENNETE_RELATION,
NOMBRE_BENOUN AS STOCK_NOMBRE,
0 AS PRODUCTION_NOMBRE,
0 AS PRODUCTTION_VOLUME_TND,
0 AS PRODUCTTION_VOLUME_DEV,
0 AS STOCK_VOLUME_DEV,
0 AS STOCK_VOLUME_TND,
SOUS_MARCHE,
TYPE_SOUS_MARCHE,
SEGMENT_2015 as SEGMENT_N_1
FROM
C:\pfe\DONNEES_RECAP\BENOUN.qvd
(qvd);
LOAD
rowno() as tassurance_id,
'INCENDIE' AS PRODUIT,
STAUT_CLIENT AS STATUT_CLIENT,
MARCHE,ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATION,
%ID_AGENCE_CTOS_CLIENT AS AGENCE,
AGE_RELATION,
DESCRIPTION_CATEGORIE_CLIENT,
%ID_CATEGORIE_CLIENT,
'TND' AS DEVISE,
'INCENDIE' AS NATURE,
'PRODUITS_FINANCIER' AS TYPE_DESC,
'ASSSURANCE' AS SOUS_TYPE_DESC,
'PROD_FIN' AS TYPE,
'ASS' AS SOUS_TYPE,
PROD_INCENDIE AS PROD_MOIS,
SEGMENT,
CONQUETE,
0 AS ATTRITION, DESCRIPTION_MARCHE_CLIENT,
STATUT_INCENDIE AS STATUT_PROD,
ANCIENNETE_RELATION,
NOMBRE_INCENDIE AS STOCK_NOMBRE,
0 AS PRODUCTION_NOMBRE,
0 AS PRODUCTTION_VOLUME_TND,
0 AS PRODUCTTION_VOLUME_DEV,
0 AS STOCK_VOLUME_DEV,
0 AS STOCK_VOLUME_TND,
SOUS_MARCHE,
TYPE_SOUS_MARCHE,
SEGMENT_2015 as SEGMENT_N_1
FROM
(qvd);
UnQualify *;
[ASSURANCE]:
LOAD
rowno() as tassurance_id,
'PROJET_AVENIR' AS PRODUIT,
STAUT_CLIENT AS STATUT_CLIENT,
MARCHE,ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATION,
%ID_AGENCE_CTOS_CLIENT AS AGENCE,
AGE_RELATION,
DESCRIPTION_CATEGORIE_CLIENT,
%ID_CATEGORIE_CLIENT,
'TND' AS DEVISE,
'PROJET_AVENIR' AS NATURE,
'PRODUITS_FINANCIER' AS TYPE_DESC,
'ASSSURANCE' AS SOUS_TYPE_DESC,
'PROD_FIN' AS TYPE,
'ASS' AS SOUS_TYPE,
PROD_PA AS PROD_MOIS,
SEGMENT,
0 AS ATTRITION,
0 AS CONQUETE, DESCRIPTION_MARCHE_CLIENT,
STATUT_PA AS STATUT_PROD,
ANCIENNETE_RELATION,
NOMBRE_PA AS STOCK_NOMBRE,
0 AS PRODUCTION_NOMBRE,
0 AS PRODUCTTION_VOLUME_TND,
0 AS PRODUCTTION_VOLUME_DEV,
ENCOURS_PA AS STOCK_VOLUME_DEV,
ENCOURS_PA AS STOCK_VOLUME_TND,
SOUS_MARCHE,
TYPE_SOUS_MARCHE,
SEGMENT_2015 as SEGMENT_N_1
FROM
C:\pfe\DONNEES_RECAP\PA.qvd
(qvd);
LOAD
rowno() as tassurance_id,
'FAMILIA' AS PRODUIT,
STAUT_CLIENT AS STATUT_CLIENT,
MARCHE,ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATION,
%ID_AGENCE_CTOS_CLIENT AS AGENCE,
AGE_RELATION,
DESCRIPTION_CATEGORIE_CLIENT,
%ID_CATEGORIE_CLIENT,
'TND' AS DEVISE,
'FAMILIA' AS NATURE,
'PRODUITS_FINANCIER' AS TYPE_DESC,
'ASSSURANCE' AS SOUS_TYPE_DESC,
'PROD_FIN' AS TYPE,
'ASS' AS SOUS_TYPE,
PROD_FAMILIA AS PROD_MOIS,
SEGMENT,
0 AS ATTRITION,
0 AS CONQUETE, DESCRIPTION_MARCHE_CLIENT,
STATUT_FAMILIA AS STATUT_PROD,
ANCIENNETE_RELATION,
NOMBRE_FAMILIA AS STOCK_NOMBRE,
0 AS PRODUCTION_NOMBRE,
0 AS PRODUCTTION_VOLUME_TND,
0 AS PRODUCTTION_VOLUME_DEV,
0 AS STOCK_VOLUME_DEV,
0 AS STOCK_VOLUME_TND,
SOUS_MARCHE,
TYPE_SOUS_MARCHE,
SEGMENT_2015 as SEGMENT_N_1
FROM
C:\pfe\DONNEES_RECAP\FAMILIA.qvd
(qvd);
LOAD
rowno() as tassurance_id,
'BENOUN' AS PRODUIT,
STAUT_CLIENT AS STATUT_CLIENT,
MARCHE,ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATION,
%ID_AGENCE_CTOS_CLIENT AS AGENCE,
AGE_RELATION,
DESCRIPTION_CATEGORIE_CLIENT,
%ID_CATEGORIE_CLIENT,
'TND' AS DEVISE,
'BENOUN' AS NATURE,
'PRODUITS_FINANCIER' AS TYPE_DESC,
'ASSSURANCE' AS SOUS_TYPE_DESC,
'PROD_FIN' AS TYPE,
'ASS' AS SOUS_TYPE,
PROD_BENOUN AS PROD_MOIS,
SEGMENT,
CONQUETE,
0 AS ATTRITION, DESCRIPTION_MARCHE_CLIENT,
STATUT_BENOUN AS STATUT_PROD,
ANCIENNETE_RELATION,
NOMBRE_BENOUN AS STOCK_NOMBRE,
0 AS PRODUCTION_NOMBRE,
0 AS PRODUCTTION_VOLUME_TND,
0 AS PRODUCTTION_VOLUME_DEV,
0 AS STOCK_VOLUME_DEV,
0 AS STOCK_VOLUME_TND,
SOUS_MARCHE,
TYPE_SOUS_MARCHE,
SEGMENT_2015 as SEGMENT_N_1
FROM
C:\pfe\DONNEES_RECAP\BENOUN.qvd
(qvd);
LOAD
rowno() as tassurance_id,
'INCENDIE' AS PRODUIT,
STAUT_CLIENT AS STATUT_CLIENT,
MARCHE,ANNEE & '-' & IF(MOIS ='janv.','01',IF(MOIS='févr.','02',IF(MOIS='mars','03',IF(MOIS='avr.','04',IF(MOIS='mai','05',IF(MOIS='juin','06',IF(MOIS='juil.','07',IF(MOIS='août','08',IF(MOIS='sept.','09',IF(MOIS='oct.','10',IF(MOIS='nov.','11','12'))))))))))) AS SITUATION,
%ID_AGENCE_CTOS_CLIENT AS AGENCE,
AGE_RELATION,
DESCRIPTION_CATEGORIE_CLIENT,
%ID_CATEGORIE_CLIENT,
'TND' AS DEVISE,
'INCENDIE' AS NATURE,
'PRODUITS_FINANCIER' AS TYPE_DESC,
'ASSSURANCE' AS SOUS_TYPE_DESC,
'PROD_FIN' AS TYPE,
'ASS' AS SOUS_TYPE,
PROD_INCENDIE AS PROD_MOIS,
SEGMENT,
CONQUETE,
0 AS ATTRITION, DESCRIPTION_MARCHE_CLIENT,
STATUT_INCENDIE AS STATUT_PROD,
ANCIENNETE_RELATION,
NOMBRE_INCENDIE AS STOCK_NOMBRE,
0 AS PRODUCTION_NOMBRE,
0 AS PRODUCTTION_VOLUME_TND,
0 AS PRODUCTTION_VOLUME_DEV,
0 AS STOCK_VOLUME_DEV,
0 AS STOCK_VOLUME_TND,
SOUS_MARCHE,
TYPE_SOUS_MARCHE,
SEGMENT_2015 as SEGMENT_N_1
FROM
(qvd);
Did you try with RecNo() or you can take a final Resident Load of the Assurance table to create a RowNo()
[FINAL_ASSURANCE]:
LOAD *,
RowNo() as New_tassurance_id
Resident ASSURANCE;
DROP Table ASSURANCE;
RENAME Table FINAL_ASSURANCE as ASSURANCE;
this should have worked!
try adding CONCATENATE keyword between the table loads
i added but didnt work
Would you mind telling what worked? RecNo() or Resident approach?
Seems like RowNo() should have worked as Vineeth mentioned... tried it with this
Table:
LOAD RecNo() as RecNoCounter,
RowNo() as RowNoCounter
AutoGenerate 10;
LOAD RecNo() as RecNoCounter,
RowNo() as RowNoCounter
AutoGenerate 10;