topic Re: Auto_increment column in QlikView

Auto_increment column

souadouert — Mon, 13 Mar 2017 13:02:55 GMT

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

Re: Auto_increment column

sunny_talwar — Mon, 13 Mar 2017 13:03:51 GMT

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

Re: Auto_increment column

vinieme12 — Mon, 13 Mar 2017 13:13:53 GMT

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

Re: Auto_increment column

souadouert — Mon, 13 Mar 2017 13:15:41 GMT

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,

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

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

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

Re: Auto_increment column

souadouert — Mon, 13 Mar 2017 13:15:59 GMT

UnQualify *;

[ASSURANCE]:

LOAD

rowno() as tassurance_id,

'PROJET_AVENIR' AS PRODUIT,

STAUT_CLIENT AS STATUT_CLIENT,

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

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

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

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

Re: Auto_increment column

sunny_talwar — Mon, 13 Mar 2017 13:18:46 GMT

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;

Re: Auto_increment column

vinieme12 — Mon, 13 Mar 2017 13:21:31 GMT

this should have worked!

try adding CONCATENATE keyword between the table loads

Re: Auto_increment column

souadouert — Mon, 13 Mar 2017 13:28:34 GMT

i added but didnt work

Re: Auto_increment column

sunny_talwar — Mon, 13 Mar 2017 13:29:36 GMT

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

Re: Auto_increment column

sunny_talwar — Mon, 13 Mar 2017 13:32:26 GMT

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;

Re: Auto_increment column

souadouert — Mon, 13 Mar 2017 14:16:59 GMT

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;

Re: Auto_increment column

souadouert — Mon, 13 Mar 2017 14:18:37 GMT

SUNNY

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

Re: Auto_increment column

sunny_talwar — Mon, 13 Mar 2017 14:20:28 GMT

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

Re: Auto_increment column

souadouert — Mon, 13 Mar 2017 14:23:35 GMT

LinkTable:

LOAD tCAPITALCLIENT_id,

AGENCECAPITAL as AGENCEC

resident [CAPITALCLIENT];

Concatenate

LOAD tPACK_id,

AGENCEPACK as AGENCEC

resident [PACKS];

drop fields

AGENCEPACK,AGENCECAPITAL

Re: Auto_increment column

souadouert — Mon, 13 Mar 2017 14:24:16 GMT

Qualify *;

[CAPITALCLIENTF]:

LOAD

'CAPITAL_CLIENT' AS PRODUIT,

AS SITUATIONCAPITAL,

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

FROM

C:\pfe\DONNEES_RECAP\CAPITAL_CLIENT.qvd

(qvd);

[CAPITALCLIENT]:

LOAD *,

RowNo() as tCAPITALCLIENT_id

Resident [CAPITALCLIENTF];

DROP Table CAPITALCLIENTF;

Re: Auto_increment column

souadouert — Mon, 13 Mar 2017 14:24:43 GMT

UNQUALIFY *;

[PACKSF]:

LOAD

'PACKS' AS PRODUIT,

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;

Re: Auto_increment column

sunny_talwar — Mon, 13 Mar 2017 14:30:11 GMT

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

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

Re: Auto_increment column

souadouert — Mon, 13 Mar 2017 14:43:44 GMT

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

Re: Auto_increment column

sunny_talwar — Mon, 13 Mar 2017 14:47:39 GMT

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.

Re: Auto_increment column

souadouert — Mon, 13 Mar 2017 14:49:07 GMT

i understand thank you sunny