Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
May be this:
Qualify * ;
Unqualify ColumnName1,CoumnName2;
load *
From table;
didnt work
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;
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;
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
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;
thank you Aar
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?
yes carlos,Thank you for your explanation, so i have unqualify the table first after that i put qualify, and it works