Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
souadouert
Specialist
Specialist

Auto_increment column

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

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

20 Replies
sunny_talwar

May be try RecNo() if you have Preceding Load on top of the table where you used RowNo()?

vinieme12
Champion III
Champion III

how did you load the tables? can you post the script?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
souadouert
Specialist
Specialist
Author

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

souadouert
Specialist
Specialist
Author

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

sunny_talwar

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;

vinieme12
Champion III
Champion III

this should have worked!

try adding CONCATENATE keyword between the table loads

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
souadouert
Specialist
Specialist
Author

i added but didnt work

sunny_talwar

Would you mind telling what worked? RecNo() or Resident approach?

sunny_talwar

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;

Capture.PNG