resident and joins

[Anonymous]

Hi

i have an issue when creating a qvd file from a resident table that i have created after i uploaded and joined 2 tables. The script looks something like this:

TEMP:

Load

     RepID

     DATE

     ProductID

     FamilyProductID

     Sales

     Measure2

     ...

from qvd

LEFT JOIN

Load

     RepsID

     DATE

     RepBoss

     RepSupervisor

      ...

from qvd

//THEN I CREATE A TABLE THAT PUT TOGETHER INFORMATION FROM THE 2 ABOVE//

DATAMART:

Load

RepsID

     DATE

     RepBoss

     RepSupervisor

ProductID

     FamilyProductID

     Sales

FROM TEMP

STORE AS QVD

My problem is that in my datamart i dont see the Rep Supervisor? What am i doing wrong?

TX a lot

[its_anandrjs]

Hi,

Confirm and check did you follow the below script as it is for creating join and then create the table with store command And what is the common field for join in this two tables i think it will be RepID if so then use same name in the tables.

TEMP:

Load

     RepID,

     DATE,

     ProductID,

     FamilyProductID,

     Sales,

     Measure2,

     ...

From qvd

LEFT JOIN

Load

     RepsID as RepID,

     RepsID,

     DATE,

     RepBoss,

     RepSupervisor,

      ...

from qvd

DATAMART:

Load

     RepID,

     RepsID,

     DATE,

     RepBoss,

     RepSupervisor,

     ProductID,

     FamilyProductID,

     Sales

Resident TEMP;

STORE DATAMART AS DATAMART.QVD;

Drop table TEMP;

Regards

Anand

[#3]

Hi Niccolo,

you have RepID in the first table and RepsID in the second, if you want to join on the ID the fields will need to be the same name.

Based on that the only field you are currently joining on is DATE, which if it was working correctly would be giving you have big duplication in your temp table, so check the formats of DATE in the two tables.

Also, the only extra element you are doing in your final table looks to be loading a field subset (dropping Measure2),

you can just use 'drop field' and then just store the temp table, no real need to do the resident

hope that helps

Joe

[Anonymous]

a

[Anonymous]

Also I am having some issues with formatting of DATA_COMPETENZA that does not allow to link properly the 2 tables

[Anonymous]

RepID and RepsID are not a problem (it is my typo). And this is a simplification of my script, i have many measures.

The script is actually this:

TEMP:

LOAD COD_FB,

date(DATA_COMPETENZA,'MM/YYYY') as DATA_COMPETENZA,

//COD_PRODOTTO,

//TIPO_PRODOTTO,

//TIPO_COMPARTO,

DES_Prodotto_TipoRisparmio1,

DES_Prodotto_BusinessLine2,

DES_Prodotto_Famiglia3,

DES_Prodotto,

NAV,

FFEE_PERS,

FFEE_OVER,

MFEE_PERS,

MFEE_OVER,

     IMP_RACCOLTA_NETTA

FROM

ETL1_Dati_Finanziari.qvd(qvd)

Where year(DATA_COMPETENZA)>='2013';

//LEFT JOIN

LOAD

//ANNO,

//MESE,

date(DATA_COMPETENZA,'MM/YYYY') as DATA_COMPETENZA,

COD_Regional ,

DES_Regional ,

COD_2Lvl ,

DES_2Lvl ,

COD_3Lvl,

DES_3Lvl ,

NOME_FB ,

COD_FB,

DATA_INGRESSO_FB,

//DATA_NASCITA_FB,

DATA_USCITA_FB,

LIVELLO_CARRIERA,

PERC_PROVVIGIONALE,

RUOLO_COMMERCIALE,

TIPO_PROVENIENZA,

COD_AGENZIA,

QUALIFICA,

TIPOLOGIA,

     [DT APERTURA],

DES_Ripartizione,

DES_Provincia,

DES_Regione,

DES_Comune

FROM

ETL1_All_info_FB.qvd (qvd)

Where year(DATA_COMPETENZA)>='2013';

//Datamart_Prodotti:

//LOAD

// month(DATA_COMPETENZA) as PDT_DTA_Mese,

// year(DATA_COMPETENZA) as PDT_DTA_Anno,

// DATA_COMPETENZA as DTA_MAKEDATE,

//     DES_Prodotto_TipoRisparmio1,

// DES_Prodotto_BusinessLine2,

// DES_Prodotto_Famiglia3,

// DES_Prodotto,

// COD_Regional ,

// DES_Regional ,

// COD_2Lvl ,

// DES_2Lvl ,

// COD_3Lvl,

// DES_3Lvl ,

//     NOME_FB ,

//     COD_FB,

//// COD_Regional as PDT_COD_Regional,

//// DES_Regional as PDT_DES_Regional,

//// COD_2Lvl as PDT_COD_2Lvl,

//// DES_2Lvl as PDT_DES_2Lvl,

//// COD_3Lvl as PDT_COD_3Lvl,

//// DES_3Lvl as PDT_DES_3Lvl,

//// NOME_FB as PDT_DES_FB,

//// COD_FB as PDT_COD_FB,

// if(Year(DATA_INGRESSO_FB)= 2014,1,0) as PDT_FLG_Ingresso,

// if(Year(DATA_USCITA_FB)=2014,1,0) as PDT_FLG_Uscito,

// NAV as PDT_DBL_AUM,

// IMP_RACCOLTA_NETTA as PDT_DBL_RN,

// MFEE_PERS as PDT_DBL_MGdirette,

// MFEE_OVER as PDT_DBL_MGover,

// FFEE_PERS as PDT_DBL_FFdirette,

// FFEE_OVER as PDT_DBL_FFover,

// MFEE_PERS+MFEE_OVER+FFEE_PERS+FFEE_OVER as PDT_DBL_Remunerazione,

// FFEE_PERS+FFEE_OVER as PDT_DBL_Front,

// MFEE_PERS+MFEE_OVER as PDT_DBL_Management,

// MFEE_OVER+FFEE_OVER as PDT_DBL_Over,

// MFEE_PERS+FFEE_PERS as PDT_DBL_Dirette

//   

//Resident TEMP;

//STORE Datamart_Prodotti into ETL2_Datamart_Prodotti.qvd(qvd);

//DROP Tables TEMP,Datamart_Prodotti;