Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
thanks. so how can i edit the script ? what should i add?
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;
thanks Nicolas.. is it possible not to delete the tables? because in need them in other things
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.
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
Just use set-expressions.
in the table box condition?
In your visualization expression.