Issues with Link Table

[bmenicucci]

Hello folks,

I need your help with a strange thing it happens to me. Consider the following script:

>>>>>>>>>>>>>>>>>>>

AreaBusinessMap:

MAPPING LOAD

   TipoBusiness

   ,%IDTipoBusiness

FROM $(vG.LoadPath)\AreeBusiness.qvd (qvd);

FatturatoInterventi:

LOAD DISTINCT

    %IDFattInterventi

    , IDFattInterventi

    , IDKeyVendite

    , ApplyMap('AreaBusinessMap', TipoBusiness, '<Null>') AS %IDTipoBusiness

    , ApplyMap('AreaBusinessMap', TipoBusiness, '<Null>') AS %CheckTipoBusiness

    , DataIntervento

    , TipoDocInt

    , Teci_nteKey

    , IDTecnico

    , [Serial Intervento]

    , [Provenienza Intervento]

    , NUMTECINT

    , FlagProvenienza

    , ValoreMovInt

FROM $(vG.ExtractPath)\TempFatturatoInterventi.qvd (qvd);

AreeBusiness:

LOAD

   TipoBusiness

   ,%IDTipoBusiness

FROM $(vG.LoadPath)\AreeBusiness.qvd (qvd);

>>>>> NOW LOADING TABLES IN A LINK TABLE <<<<<<<<<<<<<

LinkTable:

//Carico i tipi di Business nella Link Table

LOAD Distinct

%IDTipoBusiness

Resident AreeBusiness;

//Carico FatturatoInterventi nella Link Table

CONCATENATE

LOAD Distinct

%IDFattInterventi

,%IDTipoBusiness

,'Fatturato Interventi' AS %DateType

Resident FatturatoInterventi;

DROP Fields

%IDTipoBusiness

,IDTecnico

From FatturatoInterventi;

I have this two tables: the linking field is TipoBusiness. The strange thing is that if I load the two table without putting them in a "link table" I get the following:

otherwise, when loading in a link table, it seems that the linking field is not recognized and I get a full join!

am I missing something?

Any help is much appreciate!

Thanks a lot.

Brunello

[Digvijay_Singh]

Not sure but do you need CONCATENATE here when using link table? May be Left JOIN is needed.

[Anonymous]

Hi,

When you concatenate, you will have a full join.

if you want to join the tables, using the link field, you need to use left join or right join.

Regards,

[bmenicucci]

Yes, actually the second table has a CONCATENATE LOAD

thanks

[bmenicucci]

Hi, thanks for your help. In this case, however, I DON'T want a full join. What I need is a join when the related fields are the same. In any case, a LEFT JOIN has not solved the problem.

[sasiparupudi1]

AreaBusinessMap:

MAPPING LOAD

   TipoBusiness

   ,%IDTipoBusiness

FROM $(vG.LoadPath)\AreeBusiness.qvd (qvd);

FatturatoInterventi:

LOAD DISTINCT

    %IDFattInterventi

    , IDFattInterventi

    , IDKeyVendite

    , ApplyMap('AreaBusinessMap', TipoBusiness, '<Null>') AS %IDTipoBusiness

    ,%IDFattInterventi &'|'& ApplyMap('AreaBusinessMap', TipoBusiness, '<Null>')  AS %Key

    , ApplyMap('AreaBusinessMap', TipoBusiness, '<Null>') AS %CheckTipoBusiness

    , DataIntervento

    , TipoDocInt

    , Teci_nteKey

    , IDTecnico

    , [Serial Intervento]

    , [Provenienza Intervento]

    , NUMTECINT

    , FlagProvenienza

    , ValoreMovInt

FROM $(vG.ExtractPath)\TempFatturatoInterventi.qvd (qvd);

AreeBusiness:

LOAD

   TipoBusiness

   ,%IDTipoBusiness

FROM $(vG.LoadPath)\AreeBusiness.qvd (qvd);

>>>>> NOW LOADING TABLES IN A LINK TABLE <<<<<<<<<<<<<

LinkTable:

//Carico i tipi di Business nella Link Table

LOAD Distinct

     %IDTipoBusiness

Resident AreeBusiness;

//Carico FatturatoInterventi nella Link Table

CONCATENATE

LOAD Distinct

     %Key

Resident FatturatoInterventi;

DROP Fields

%IDTipoBusiness

,IDTecnico

From FatturatoInterventi;

[bmenicucci]

Hello Sasidhar, thanks for your reply; however your suggestions doesn't create a relation between the two tables. I mean: selecting a record in "AreeBusiness" does not load any record in "FatturatoInterventi".

[bmenicucci]

Update: actually, loading **both** %Key and %IDTipoBusiness works! It load exactly all the records without duplicating. Thanks @Sasidhar Parupudi for leading me in the right direction! I wouldn't have done it without your help

By the way, I would like someone to enlight me as I've not understood the reason why it would work as normally.

Thank you all, folks!

[sasiparupudi1]

Great that you could solve your problem..please close this thread by marking any helpful and a correct answer.

[mikaelsc]

Hello Bruno

I don't see why you're creating a link table in your case.

Link tables make sense when you have multiple large (fact) tables that share a series of common dimensions, but for some (good or bad) reason, you want to keep those tables separated.

(there are a lot of good references like Concatenate vs Link Table)

In your case you are "just" creating an association between a fact table "FatturatoInterventi" and the AreeBusiness (dimension table).

And that is even unnecessary as you used the mapping table at first to bring the TipoBusiness field into your fact table with the apply map.

you ended  your script by removing the key field between your 2 tables (%IDTipoBusiness)

this explains why when creating a table like you did, you duplicate all of your lines by the number of TipoBusiness values that you have (3) (8226 8227 8232), which is obviously incorrect (as you already knew by yourself with the %TypoBusinessCheck field)