Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bmenicucci
New Contributor III

Issues with Link Table

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:

Screenshot 2018-04-06 18.59.23.png

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

Screenshot 2018-04-06 19.00.03.png

am I missing something?

Any help is much appreciate!

Thanks a lot.

Brunello

Tags (1)
10 Replies
Digvijay_Singh
Honored Contributor III

Re: Issues with Link Table

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

kawamuram
Contributor II

Re: Issues with Link Table

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
New Contributor III

Re: Issues with Link Table

Yes, actually the second table has a CONCATENATE LOAD

thanks

bmenicucci
New Contributor III

Re: Issues with Link Table

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
Honored Contributor III

Re: Issues with Link Table

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
New Contributor III

Re: Issues with Link Table

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
New Contributor III

Re: Issues with Link Table

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
Honored Contributor III

Re: Issues with Link Table

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

Highlighted
mikaelsc
Contributor III

Re: Issues with Link Table

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)

Community Browser