topic How to Add 'Missing' Records in Script in App Development

How to Add 'Missing' Records in Script

benvatvandata — Sat, 16 Nov 2024 16:53:14 GMT

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.

Please let me know the best method of doing this.

Thanks,

Ben

Re: How to Add 'Missing' Records in Script

chrismarlow — Tue, 07 Dec 2021 19:28:33 GMT

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.

Re: How to Add 'Missing' Records in Script

benvatvandata — Fri, 10 Dec 2021 16:54:35 GMT

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?

Re: How to Add 'Missing' Records in Script

chrismarlow — Fri, 10 Dec 2021 17:11:46 GMT

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.