Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

momofiore
New Contributor III

joining 3 tables and loadind specific data

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

10 Replies
MVP
MVP

Re: joining 3 tables and loadind specific data

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

Re: joining 3 tables and loadind specific data

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

niclaz79
Contributor III

Re: joining 3 tables and loadind specific data

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

Re: joining 3 tables and loadind specific data

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

niclaz79
Contributor III

Re: joining 3 tables and loadind specific data

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

Re: joining 3 tables and loadind specific data

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

Re: joining 3 tables and loadind specific data

Just use set-expressions.

momofiore
New Contributor III

Re: joining 3 tables and loadind specific data

in the table box condition?

niclaz79
Contributor III

Re: joining 3 tables and loadind specific data

In your visualization expression.

Community Browser