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

20 Replies
souadouert
Specialist
Specialist
Author

VINEETH

UNQUALIFY *;

[PACKSF]:

LOAD 

'PACKS' AS PRODUIT,

    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 SITUATIONPACK, 

     STAUT_CLIENT AS STATUT_CLIENT,

     MARCHE,

     DESCRIPTION_MARCHE_CLIENT,

      %ID_AGENCE_CTOS_PACK AS AGENCEPACK,

     AGE_RELATION,

     DESCRIPTION_CATEGORIE_CLIENT,

     %ID_CATEGORIE_CLIENT,

     %ID_TYPE_PACK AS SOUS_TYPE,

     LIB_TYPE_PACK AS SOUS_TYPE_DESC,

     SEGMENT, 'PACK' AS TYPE,

     0 AS CONQUETE,

     ANCIENNETE_RELATION,

     NOMBRE_PACK AS STOCK_NOMBRE,

     0 AS PRODUCTTION_VOLUME_TND,

     0 AS PRODUCTTION_VOLUME_DEV,

     0 AS STOCK_VOLUME_DEV,

     0 AS STOCK_VOLUME_TND,

     0 AS PRODUCTION_NOMBRE,

     0 AS ATTRITION,

     0 AS PROD_MOIS,

     STATUT_PACK AS STATUT_PROD, 'PACK' AS NATURE,

     'XXX' AS DEVISE,'PACK' AS TYPE_DESC,

      SOUS_MARCHE,

     TYPE_SOUS_MARCHE,

     SEGMENT_2015 as SEGMENT_N_1

FROM

(qvd);

[PACKS]:

LOAD *,

           RowNo() as tPACK_id

Resident [PACKSF];

DROP Table PACKSF;

souadouert
Specialist
Specialist
Author

SUNNY

the table loaded correctly ,and I have implemented a linktable link table  an error is displayed says that this column does not exist

sunny_talwar

Can you post a screenshot of the table and/or share the script you added for linktable?

souadouert
Specialist
Specialist
Author

LinkTable:

LOAD  tCAPITALCLIENT_id,

AGENCECAPITAL as AGENCEC

resident  [CAPITALCLIENT];

Concatenate

LOAD tPACK_id,

AGENCEPACK as AGENCEC

resident  [PACKS];

drop fields

AGENCEPACK,AGENCECAPITAL

souadouert
Specialist
Specialist
Author

Qualify *;

[CAPITALCLIENTF]:

LOAD

'CAPITAL_CLIENT' AS PRODUIT,

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 SITUATIONCAPITAL,     

  .................................................

FROM

C:\pfe\DONNEES_RECAP\CAPITAL_CLIENT.qvd

(qvd);

[CAPITALCLIENT]:

LOAD *,

           RowNo() as tCAPITALCLIENT_id

Resident [CAPITALCLIENTF];

DROP Table CAPITALCLIENTF;

souadouert
Specialist
Specialist
Author

UNQUALIFY *;

[PACKSF]:

LOAD 

'PACKS' AS PRODUIT,

    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 SITUATIONPACK, 

     STAUT_CLIENT AS STATUT_CLIENT,

     MARCHE,

     DESCRIPTION_MARCHE_CLIENT,

      %ID_AGENCE_CTOS_PACK AS AGENCEPACK,

     AGE_RELATION,

    ...................................................

     SEGMENT_2015 as SEGMENT_N_1

FROM

(qvd);

[PACKS]:

LOAD *,

           RowNo() as tPACK_id

Resident [PACKSF];

DROP Table PACKSF;

sunny_talwar

The fields in the table CAPITALCLIENTF is qualified... is that intentional?

Capture.PNG

If you want to keep Qualify, then use the link table like this:


LinkTable:

LOAD  CAPITALCLIENT.tCAPITALCLIENT_id,

     CAPITALCLIENT.CAPITALCLIENTF.AGENCECAPITAL as AGENCEC

resident  [CAPITALCLIENT];

But, I would think that you don't need the Qualify there


souadouert
Specialist
Specialist
Author

I HAVE MANY OTHER TABLE WITH SERVAL SAME COLOMN NAME so i need to use qualify

sunny_talwar

Then the field names within your CAPITALCLIENT and CAPITALCLIENTF table are qualified to include the table name. So, instead of using just the field name, you need to use the qualified field names.

souadouert
Specialist
Specialist
Author

i understand   thank you sunny