Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
benvatvandata
Partner - Creator II
Partner - Creator II

How to Add 'Missing' Records in Script

Hi,

Is there a way to achieve the below through scripting (without doing Inline loads)? I need all Names (in Dim1 table) to have records for all PIs (in Dim2 table) in my Fact table. The extra records I want added (bolded text in screenshot) to the new Fact table can just have null values for Value field.

benvatvandata_0-1638900540306.png

Please let me know the best method of doing this.

Thanks,

Ben

 

Labels (4)
3 Replies
chrismarlow
Specialist II
Specialist II

Hi,

Maybe something like this;

Dim1:
load * inline [
NameFL
abc
def
ghi
];

Dim2:
load * inline [
Pt
1
2
];

Fact:
load * inline [
NameFL, Pt, Value
abc, 1, 5
ghi
];

Join (Dim1)
Load
	*
Resident Dim2;

Outer Join (Dim1)
Load
	*
Resident Fact;

FinalFact:
NoConcatenate
Load * 
Resident Dim1
Where Not IsNull(Pt) and Len(Pt)>0;

DROP TABLES Dim1, Dim2, Fact;

Cheers,

Chris.

benvatvandata
Partner - Creator II
Partner - Creator II
Author

Hi Chris,

This does help. Unfortunately, my actual data model is much larger though so it'll be a bit more challenging to implement this. If there's another (or more) Dim tables that need this same process done, can I just replicate this same script with the other Dim tables? For example, let's say Dim3 is a list of TeamNames, and they all need Dim2 values (similar to what we did for Dim1)... exact same process? or will there be complications?

chrismarlow
Specialist II
Specialist II

Hi,

Yes, same process should apply you might need to look at the where clause.

This is doing a cartesian join & then your join back will be a on multiple fields, so I can see the data getting quite big quite quickly .. but think that is unavoidable.

Cheers,

Chris.