Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

souadouert
Contributor III

modify column name

I want to avoid this structure for column names

script:

Qualify *;

[DEPOTCOMPTEF]:

LOAD

  'COMPTES' 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 SITUATIONDEPOTCOMPTE,

     STAUT_CLIENT AS STATUT_CLIENT,

     MARCHE,

      DESCRIPTION_MARCHE_CLIENT,

     %ID_AGENCE_CTOS_COMPTE,

     %ID_AGENCE_CTOS_COMPTE AS AGENCEDEPOTCOMPTE, 

     AGE_RELATION,

     DESCRIPTION_CATEGORIE_CLIENT,

     %ID_CATEGORIE_CLIENT,

     %ID_DEVISE_COMPTE ,

     NATURE_COMPTE ,

     DESCRIPTION_TYPE_DEPOT_COMPTE ,

     DESCRIPTION_SOUS_TYPE_DEPOT_COMPTE ,

     %ID_TYPE_DEPOT_COMPTE ,

     %ID_SOUS_TYPE_DEPOT_COMPTE ,

     PROD_COMPTE ,

     STATUT_COMPTE ,

     SEGMENT,

     0 AS ATTRITION,

     CONQUETE,

     ANCIENNETE_RELATION,

     VENTE_COMPTE ,

     STOCK_COMPTE ,

   

      SOUS_MARCHE,

     TYPE_SOUS_MARCHE,

     SEGMENT_2015 as SEGMENT_N_1

   

From

C:\pfe\DONNEES_RECAP\DEPOTS_COMPTE.qvd

(qvd);

Qualify *;

[DEPOTCOMPTE]:

LOAD *,

  RowNo() as tDEPOTCOMPTE_id

Resident [DEPOTCOMPTEF];

DROP Table DEPOTCOMPTEF;

colonne.PNG

1 Solution

Accepted Solutions
aarkay29
Valued Contributor

Re: modify column name

Qualify *;

Unqualify AGE_RELATION,%ID_CATEGORIE_CLIENT,DESCRIPTION_SOUS_TYPE_DEPOT_COMPTE ;

[DEPOTCOMPTEF]:

LOAD

  'COMPTES' 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 SITUATIONDEPOTCOMPTE,

     STAUT_CLIENT AS STATUT_CLIENT,

     MARCHE,

      DESCRIPTION_MARCHE_CLIENT,

     %ID_AGENCE_CTOS_COMPTE,

     %ID_AGENCE_CTOS_COMPTE AS AGENCEDEPOTCOMPTE,

     AGE_RELATION,

     DESCRIPTION_CATEGORIE_CLIENT,

     %ID_CATEGORIE_CLIENT,

     %ID_DEVISE_COMPTE ,

     NATURE_COMPTE ,

     DESCRIPTION_TYPE_DEPOT_COMPTE ,

     DESCRIPTION_SOUS_TYPE_DEPOT_COMPTE ,

     %ID_TYPE_DEPOT_COMPTE ,

     %ID_SOUS_TYPE_DEPOT_COMPTE ,

     PROD_COMPTE ,

     STATUT_COMPTE ,

     SEGMENT,

     0 AS ATTRITION,

     CONQUETE,

     ANCIENNETE_RELATION,

     VENTE_COMPTE ,

     STOCK_COMPTE ,

  

      SOUS_MARCHE,

     TYPE_SOUS_MARCHE,

     SEGMENT_2015 as SEGMENT_N_1

  

From

C:\pfe\DONNEES_RECAP\DEPOTS_COMPTE.qvd

(qvd);

Qualify *;

[DEPOTCOMPTE]:

LOAD *,

  RowNo() as tDEPOTCOMPTE_id

Resident [DEPOTCOMPTEF];

DROP Table DEPOTCOMPTEF;

13 Replies
aarkay29
Valued Contributor

Re: modify column name

May be this:

Qualify * ;

Unqualify ColumnName1,CoumnName2;

load *

     From table;

souadouert
Contributor III

Re: modify column name

didnt work

aarkay29
Valued Contributor

Re: modify column name

Qualify *;

Unqualify AGE_RELATION,%ID_CATEGORIE_CLIENT,DESCRIPTION_SOUS_TYPE_DEPOT_COMPTE ;

[DEPOTCOMPTEF]:

LOAD

  'COMPTES' 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 SITUATIONDEPOTCOMPTE,

     STAUT_CLIENT AS STATUT_CLIENT,

     MARCHE,

      DESCRIPTION_MARCHE_CLIENT,

     %ID_AGENCE_CTOS_COMPTE,

     %ID_AGENCE_CTOS_COMPTE AS AGENCEDEPOTCOMPTE,

     AGE_RELATION,

     DESCRIPTION_CATEGORIE_CLIENT,

     %ID_CATEGORIE_CLIENT,

     %ID_DEVISE_COMPTE ,

     NATURE_COMPTE ,

     DESCRIPTION_TYPE_DEPOT_COMPTE ,

     DESCRIPTION_SOUS_TYPE_DEPOT_COMPTE ,

     %ID_TYPE_DEPOT_COMPTE ,

     %ID_SOUS_TYPE_DEPOT_COMPTE ,

     PROD_COMPTE ,

     STATUT_COMPTE ,

     SEGMENT,

     0 AS ATTRITION,

     CONQUETE,

     ANCIENNETE_RELATION,

     VENTE_COMPTE ,

     STOCK_COMPTE ,

  

      SOUS_MARCHE,

     TYPE_SOUS_MARCHE,

     SEGMENT_2015 as SEGMENT_N_1

  

From

C:\pfe\DONNEES_RECAP\DEPOTS_COMPTE.qvd

(qvd);

Qualify *;

[DEPOTCOMPTE]:

LOAD *,

  RowNo() as tDEPOTCOMPTE_id

Resident [DEPOTCOMPTEF];

DROP Table DEPOTCOMPTEF;

carlosamonroy
Contributor III

Re: modify column name

Hi Souad,

You have two 'Qualify'. That's causing the problem.

One solution, just leave one qualify, the very first one. if you do not want the next table to qualify the fields use unqualify *;

If you just want to unqualify some fields, replace the star for the field name.

Hope that helps,

Carlos M

Qualify *;

[DEPOTCOMPTEF]:

LOAD

  'COMPTES' 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 SITUATIONDEPOTCOMPTE,

    STAUT_CLIENT AS STATUT_CLIENT,

    MARCHE,

      DESCRIPTION_MARCHE_CLIENT,

    %ID_AGENCE_CTOS_COMPTE,

    %ID_AGENCE_CTOS_COMPTE AS AGENCEDEPOTCOMPTE,

    AGE_RELATION,

    DESCRIPTION_CATEGORIE_CLIENT,

    %ID_CATEGORIE_CLIENT,

    %ID_DEVISE_COMPTE ,

    NATURE_COMPTE ,

    DESCRIPTION_TYPE_DEPOT_COMPTE ,

    DESCRIPTION_SOUS_TYPE_DEPOT_COMPTE ,

    %ID_TYPE_DEPOT_COMPTE ,

    %ID_SOUS_TYPE_DEPOT_COMPTE ,

    PROD_COMPTE ,

    STATUT_COMPTE ,

    SEGMENT,

    0 AS ATTRITION,

    CONQUETE,

    ANCIENNETE_RELATION,

    VENTE_COMPTE ,

    STOCK_COMPTE ,

      SOUS_MARCHE,

    TYPE_SOUS_MARCHE,

    SEGMENT_2015 as SEGMENT_N_1

From

C:\pfe\DONNEES_RECAP\DEPOTS_COMPTE.qvd

(qvd);

Qualify *;

unqualify fieldname1, fieldname2, etc;

[DEPOTCOMPTE]:

LOAD *,

  RowNo() as tDEPOTCOMPTE_id

Resident [DEPOTCOMPTEF];

DROP Table DEPOTCOMPTEF;

Re: modify column name

You could just put the rowno() within the first load whereby do you really this rowno as ID? Further do you really need to qualify all fields?

- Marcus

adamdavi3s
Honored Contributor

Re: modify column name

I agree with Marcus that this seems a little odd, but who are we to judge!

However you just need to make sure that your're using the NEW field name when doing the unqualify, you can't just use the original ones.

So this should work:

Qualify *;

[DEPOTCOMPTEF]:

LOAD

  'COMPTES' 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 SITUATIONDEPOTCOMPTE,

    STAUT_CLIENT AS STATUT_CLIENT,

    MARCHE,

      DESCRIPTION_MARCHE_CLIENT,

    %ID_AGENCE_CTOS_COMPTE,

    %ID_AGENCE_CTOS_COMPTE AS AGENCEDEPOTCOMPTE,

    AGE_RELATION,

    DESCRIPTION_CATEGORIE_CLIENT,

    %ID_CATEGORIE_CLIENT,

    %ID_DEVISE_COMPTE ,

    NATURE_COMPTE ,

    DESCRIPTION_TYPE_DEPOT_COMPTE ,

    DESCRIPTION_SOUS_TYPE_DEPOT_COMPTE ,

    %ID_TYPE_DEPOT_COMPTE ,

    %ID_SOUS_TYPE_DEPOT_COMPTE ,

    PROD_COMPTE ,

    STATUT_COMPTE ,

    SEGMENT,

    0 AS ATTRITION,

    CONQUETE,

    ANCIENNETE_RELATION,

    VENTE_COMPTE ,

    STOCK_COMPTE ,

      SOUS_MARCHE,

    TYPE_SOUS_MARCHE,

    SEGMENT_2015 as SEGMENT_N_1

From

C:\pfe\DONNEES_RECAP\DEPOTS_COMPTE.qvd

(qvd);

unqualify *;

Qualify *;

UNQUALIFY DEPOTCOMPTEF.AGE_RELATION, DEPOTCOMPTEF. %ID_CATEGORIE_CLIENT, DEPOTCOMPTEF.DESCRIPTION_SOUS_TYPE_DEPOT_COMPTE;

[DEPOTCOMPTE]:

LOAD *,

  RowNo() as tDEPOTCOMPTE_id

Resident [DEPOTCOMPTEF];

unqualify *;

DROP Table DEPOTCOMPTEF;

souadouert
Contributor III

Re: modify column name

thank you Aar

MVP
MVP

Re: modify column name

What exactly are you trying to get rid of here? Remove DEPOTCOMPTEF from all your field names or the ones that you highlighted in Yellow?

souadouert
Contributor III

Re: modify column name

yes carlos,Thank you for your explanation, so i have unqualify the table first after that i put qualify, and it works

Community Browser