joining 3 tables and loadind specific data

[momofiore]

Hey guys:

I have 3 tables   FactTicket    and    FactTicketActivity   and   Applications. i would like to join between them and load from the joind data all rows exception rows  that have    IsAutoCap=1     and   Catégorie H2= 'New IT T2'   and   ActivitySDTier =  '3'

this is my script :

FactTicket:

LOAD

%TicketKey,

%CIApplicationName,

IsAutoCap

from 

[.\..\FactTicket_Data_2017.qvd]

(qvd) ;

FactTicketActivity:

LOAD

%TicketKey,

ActivitySDTier

from

[.\..\FactTicketActivities_Data_2017.qvd]

(qvd);

Applications:

LOAD

%CIApplicationName,

Catégorie H2

from

[.\..\SD\Ref_Applications.qvd]

(qvd);

left join (FactTicket)

load * resident FactTicketActivity;

left join (FactTicket)

load * resident Applications

WHERE [Catégorie H2] <> 'New IT T2' AND ActivitySDTier <> '3' AND  IsAutoCap <> '1' ;

But i got this error 

Field not found - <ActivitySDTier>

left join (FactTicket)

load * resident Applications

WHERE [Catégorie H2] <> 'New IT T2' AND ActivitySDTier <> '3' AND  IsAutoCap <> '1'

thanks guys

[Miguel_Angel_Baeyens]

Because at the time of performing the LOAD RESIDENT that field, ActivitySDTier, is not part of the Applications table, which has only 2 fields, %CIApplicationName and Catégorie H2.

The field will only exists after the JOIN is complete, not before.

[momofiore]

thanks. so how can i edit the script ? what should i add?

[niclaz79]

FactTicket:

LOAD

%TicketKey,

%CIApplicationName,

IsAutoCap

from

[.\..\FactTicket_Data_2017.qvd]

(qvd)

;

FactTicketActivity:

LOAD

%TicketKey,

ActivitySDTier

from

[.\..\FactTicketActivities_Data_2017.qvd]

(qvd);

Applications:

LOAD

%CIApplicationName,

Catégorie H2

from

[.\..\SD\Ref_Applications.qvd]

(qvd);

left join (FactTicket)

load * resident FactTicketActivity;

drop table FactTicketActivity;

left join (FactTicket)

load * resident Applications

drop table Applications;

FinalTable:

LOAD

     *

Resident FactTicket

WHERE [Catégorie H2] <> 'New IT T2' AND ActivitySDTier <> '3' AND  IsAutoCap <> '1' ;

drop table FactTicket;

[momofiore]

thanks Nicolas.. is it possible not to delete the tables? because in need them  in other things

[niclaz79]

If you keep them you will create synthetic keys. If there is a need for all the data to be used in the application, I don't understand why you are limiting the data in the script? Just do it in the visualization instead.

[momofiore]

exactly. i will need FactTicket table in other things. but i thought it is possible to load just what i want.

but however is there a way to visualize all data exception row that have  IsAutoCap=1     and   Catégorie H2= 'New IT T2'   and   ActivitySDTier =  '3'.

I have tables in visualization , chart and other things. thank you so much Nico

[niclaz79]

Just use set-expressions.

[momofiore]

in the table box condition?

[niclaz79]

In your visualization expression.