Limiting Data in Load Editor Script

[Anonymous]

I would like to eliminate older charge data however, I need to still include older charges if their owed amount is not $0.

I came up with the where clause

Where not (((year(today(0))*12)+month(today(0))) - (((year(“Date of Service”)*12)+month(“Date of Service”))) > 25

And “Owed Amount” = 0)

My problem is that the field "Date of Service" is in the Charge_Dimension section of the data load editor and "Owed amount" is in the Master_Fact section of the data load editor so I am unsure where/how to include this where clause in the load script.

I can’t just write where not year(today(0))*12)+month(today(0))) - (((year(“Date of Service”)*12)+month(“Date of Service”))) > 25 in the Charge_Dimension section and Where not “Owed Amount = 0” in the Master_Fact section because this will give me entirely wrong results, it has to be a combination of the two parameters.

Any thoughts?

[sunny_talwar]

How about this

Where MonthStart(Today(), -25) > MonthStart([Date of Service]) or [Owed Amount] <> 0;

[Anonymous]

Hi Sunny,

My where clause is correct as is, and it needs to be AND not OR.  My issue is that I do not know where to implement this where clause in the data load editor script, as the two referenced fields are in different sections of the editor.

Thanks,
Sarah

[Gabriel]

Hi Sarah,

What I can suggest is to use the associate KeyField (used to associate,join, Fact to Dim), use the KeyField to bring "Date of Service" into the Fact table (LEFT JOIN). Doing that then you can use your WHERE clause on the FACT table.

Hopefully that helps

[sunny_talwar]

May be this

Where not (((year(today(0))*12)+month(today(0))) - (((year(“Date of Service”)*12)+month(“Date of Service”))) > 25

or “Owed Amount” = 0)

[dwforest · Accepted solution]

You'll have to use temp tables to get the data resident and then filter after you can combine the data.

Sounds like you already get all Master_Fact (or it has a "stand alone" filter). Load it first.

Create a temp table for Charge_dimension, apply any other filters you can to limit load.

Then join the Charge_Amount to the Charge_Temp to create another temp table.

Then LOAD RESIDENT from Charge_Temp2 where where not year(today(0))*12)+month(today(0))) - (((year(“Date of Service”)*12)+month(“Date of Service”))) > 25 in the Charge_Dimension section and Where not “Owed Amount = 0”

Then delete temp tables.