Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
momofiore
Creator
Creator

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
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
Creator
Creator
Author

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

niclaz79
Partner - Creator III
Partner - Creator III

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
Creator
Creator
Author

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

niclaz79
Partner - Creator III
Partner - Creator III

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
Creator
Creator
Author

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
Partner - Creator III
Partner - Creator III

Just use set-expressions.

momofiore
Creator
Creator
Author

in the table box condition?

niclaz79
Partner - Creator III
Partner - Creator III

In your visualization expression.