topic Re: "Set Analysis" in Load Script in QlikView

"Set Analysis" in Load Script

blpetosa — Wed, 16 Aug 2017 16:27:30 GMT

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

Re: "Set Analysis" in Load Script

sunny_talwar — Wed, 16 Aug 2017 16:38:04 GMT

Is this sum you are doing is per event?

Re: "Set Analysis" in Load Script

blpetosa — Wed, 16 Aug 2017 16:53:39 GMT

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?

Re: "Set Analysis" in Load Script

sunny_talwar — Wed, 16 Aug 2017 17:00:00 GMT

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

Re: "Set Analysis" in Load Script

blpetosa — Wed, 16 Aug 2017 17:11:28 GMT

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.

Re: "Set Analysis" in Load Script

blpetosa — Wed, 16 Aug 2017 17:21:04 GMT

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.

Re: "Set Analysis" in Load Script

blpetosa — Wed, 16 Aug 2017 17:30:24 GMT

* generic load

Re: "Set Analysis" in Load Script

blpetosa — Wed, 16 Aug 2017 20:34:15 GMT

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.