"Set Analysis" in Load Script

[blpetosa]

Hello Everyone,

I am basically trying to recreate set analysis in my load script. I have different types of "Goal_Types" that I need to base the output on.

The set analysis that I use for visualizations is:

sum({<Goal_Type={4}>} Goal)

This gives me a value where goal type is 4.

I need to get this into the load script so it only brings in rows where the goal is less than 30% of the actual. I got around this before by only loading in Goal_Type 4, and using this:

WHERE event/Goal <.3

Now I have to bring in 2 more Goal_Types (7 & 8), so I need to base the logic off of the different types. I need something like this:

WHERE event/(sum({<Goal_Type={4}>} Goal))<.3

OR ((sum({<Goal_Type={8}>} Goal)) - (sum({<Goal_Type={7}>} Goal))) > 0

;

Thanks in advance for any help you can offer,

Ben

[sunny_talwar]

Is this sum you are doing is per event?

[blpetosa]

Each row has a different unique identifier that has an "event" total, and I only want rows where the "Goal" is than 30% of it. I used the sum aggregation because the value is stored as a character (which I converted with NUM) in the back end, and when I use count, it will just give me 1 since there is only one value. This is a different question, but can you do set analysis without aggregation, like "just give me the value where", instead of having to count or sum? Does this answer you question?

[sunny_talwar]

1) The issue is that we will have Goal_Type = 7 and Goal_Type = 8 on different lines, so I guess I am asking that do you want to bring only those events where goals for Goal_Type = 8 minus the goals for Goal_type = 7 is greater than 0?

2) You can use Only() function because a FieldName as expression is equivalent of using Only(FieldName). The benefit here is that you can use set analysis within Only() function

[blpetosa]

1.     Yes, that is correct. I need only the rows that meet one of the two criteria above.

2.     I will try Only(), thanks for that.

[blpetosa]

Would it be better to assign those three Goal_Types as three different variables earlier on in the script? Then they would be 3 different fields, with their respective values. I believe I used a "general load" to do something like this in the past.

[blpetosa]

* generic load

[blpetosa]

I actually got it to work. I went back and split up the goal_types into separate tables with a generic load. I then used a for loop to get them all together, and joined them back into the resident table. I then loaded a new table with the goal_types as fields, resident the previous one, and joined it into the table I needed to. This the second time this week you responded quickly to an issue I was having. I really appreciate that Sunny.