topic Re: joining 3 tables and loadind specific data in QlikView

joining 3 tables and loadind specific data

momofiore — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: joining 3 tables and loadind specific data

Miguel_Angel_Baeyens — Thu, 12 Jul 2018 11:02:12 GMT

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.

Re: joining 3 tables and loadind specific data

momofiore — Thu, 12 Jul 2018 11:04:48 GMT

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

Re: joining 3 tables and loadind specific data

niclaz79 — Thu, 12 Jul 2018 11:10:43 GMT

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;

Re: joining 3 tables and loadind specific data

momofiore — Thu, 12 Jul 2018 13:21:20 GMT

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

Re: joining 3 tables and loadind specific data

niclaz79 — Thu, 12 Jul 2018 13:26:03 GMT

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.

Re: joining 3 tables and loadind specific data

momofiore — Thu, 12 Jul 2018 13:48:44 GMT

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

Re: joining 3 tables and loadind specific data

niclaz79 — Thu, 12 Jul 2018 13:53:14 GMT

Just use set-expressions.

Re: joining 3 tables and loadind specific data

momofiore — Thu, 12 Jul 2018 13:59:58 GMT

in the table box condition?

Re: joining 3 tables and loadind specific data

niclaz79 — Thu, 12 Jul 2018 14:41:30 GMT

In your visualization expression.

Re: joining 3 tables and loadind specific data

momofiore — Thu, 12 Jul 2018 14:46:38 GMT

OK. for example i have a table box with the 3 columns ActivitySDTier , IsAutoCap and Catégorie H2.

how can i visualize all rows exception those whom have IsAutoCap=1 and Catégorie H2= 'New IT T2' and ActivitySDTier = '3'

if you can give me a scearnshut i would be so thankfull Nico