topic Re: modify column name in QlikView

modify column name

souadouert — Tue, 14 Mar 2017 13:45:21 GMT

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;

Re: modify column name

aarkay29 — Tue, 14 Mar 2017 14:13:36 GMT

May be this:

Qualify * ;

Unqualify ColumnName1,CoumnName2;

load *

From table;

Re: modify column name

souadouert — Tue, 14 Mar 2017 14:19:08 GMT

didnt work

Re: modify column name

aarkay29 — Tue, 14 Mar 2017 14:40:45 GMT

Qualify *;

Unqualify AGE_RELATION,%ID_CATEGORIE_CLIENT,DESCRIPTION_SOUS_TYPE_DEPOT_COMPTE ;

[DEPOTCOMPTEF]:

LOAD

'COMPTES' AS PRODUIT,

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;

Re: modify column name

CarlosAMonroy — Tue, 14 Mar 2017 14:45:04 GMT

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,

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

marcus_sommer — Tue, 14 Mar 2017 14:47:43 GMT

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

Re: modify column name

adamdavi3s — Tue, 14 Mar 2017 14:54:31 GMT

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,

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;

Re: modify column name

souadouert — Tue, 14 Mar 2017 15:06:14 GMT

thank you Aar

Re: modify column name

sunny_talwar — Tue, 14 Mar 2017 15:08:15 GMT

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?

Re: modify column name

souadouert — Tue, 14 Mar 2017 15:08:52 GMT

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

Re: modify column name

souadouert — Tue, 14 Mar 2017 15:09:57 GMT

yes marcus because i have a linktable, with rowno at the first load doesnt work

Re: modify column name

souadouert — Tue, 14 Mar 2017 15:11:57 GMT

yes marcus because i have a linktable, with rowno at the first load doesnt work, but With Aar's solution, it works correctly

Re: modify column name

souadouert — Tue, 14 Mar 2017 15:14:03 GMT

I do not want to display the column name with a long hierarchy but i found the solution now it works correctly

Qualify *;

[PACKF]:

LOAD

'PACKS' AS PRODUIT,

%ID_AGENCE_CTOS_PACK,

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 ,

LIB_TYPE_PACK ,

SEGMENT,

'PACK' AS TYPE,

CONQUETE,

ANCIENNETE_RELATION,

NOMBRE_PACK ,

0 AS PROD_MOIS,

STATUT_PACK AS STATUT_PROD,

'PACK' AS NATURE,

SOUS_MARCHE,

TYPE_SOUS_MARCHE,

SEGMENT_2015 as SEGMENT_N_1

FROM

(qvd);

Qualify *;

[PACKS]:

LOAD *,

RowNo() as tPACK_id

Resident [PACKF];

DROP Table PACKF;

Re: modify column name

sunny_talwar — Tue, 14 Mar 2017 16:01:21 GMT

Awesome