Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Limiting Data in Load Editor Script

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?

1 Solution

Accepted Solutions
dwforest
Specialist II
Specialist II

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.

View solution in original post

5 Replies
sunny_talwar

How about this

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

Anonymous
Not applicable
Author

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
Partner - Specialist III
Partner - Specialist III

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
Specialist II
Specialist II

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.