Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
May be check out Exists function
Exists - script function ‒ QlikView
You can also look into Left Joins or Left Keeps