Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

micsim123
New Contributor III

Query Fact and Dimension tables in load script and create new table

In my load script I first load a Fact table and 10 dimension tables.


How can I in the load script create a new table, which is a subset of the total "FactDimension" table based on values in the dimension tables.


In SQL, it would be something like:


Create table

Select * from Fact

left join ....

left join ....

where dim1.fieldA = "aaa" and dim2.fieldB = "bbb" and ...


How can i do this without having to join my Fact table and the 10 dimension tables in the load script.


In other words, how can I in the load script filter data in the same way as when the load script is completed and data is loaded into Qlik Sense.


Thank you in advance for all ideas.

Michael

2 Replies
alextimofeyev
Contributor II

Re: Query Fact and Dimension tables in load script and create new table

Michael,

here is a simple sample script, is this what you are looking for?

facts:

load * inline

["RecId","RecDate","RecDealer","RecCustomer","RecAmount"

1,2017-12-01,Acme,Bob Johnson,200

2,2017-12-02,Ajax,Suzy Bloggs,100

3,2017-12-12,Punto,Pradeep Prakash,400];

dim1:

load * inline

["Dealer","DealerCity"

Acme,Washington D.C.

Punto,Mexico City];

dim2:

load * inline

["Customer","CustomerCity"

Bob Johnson,Washington D.C.

Pradeep Prakash,Guadalajara];

fullfacts:

load

RecId as FactId,

RecDate as FactDate,

RecDealer as Dealer,

RecCustomer as Customer,

RecAmount as FactAmount

resident facts

where exists (Dealer,RecDealer)

and exists (Customer,RecCustomer);

Re: Query Fact and Dimension tables in load script and create new table

May be check out Exists function

Exists - script function ‒ QlikView

You can also look into Left Joins or Left Keeps

Understanding Join, Keep and Concatenate

Community Browser