Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

skelly328
New Contributor II

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
Valued Contributor

Re: Limiting Data in Load Editor Script

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.

5 Replies

Re: Limiting Data in Load Editor Script

How about this

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

skelly328
New Contributor II

Re: Limiting Data in Load Editor Script

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
Valued Contributor II

Re: Limiting Data in Load Editor Script

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

Re: Limiting Data in Load Editor Script

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
Valued Contributor

Re: Limiting Data in Load Editor Script

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.

Community Browser