Query Fact and Dimension tables in load script and... - Qlik Community

micsim123 · ‎2017-12-14

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 ....

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

alextimofeyev · ‎2017-12-14

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);

sunny_talwar · ‎2017-12-14

May be check out Exists function

You can also look into Left Joins or Left Keeps

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