Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

blpetosa
Not applicable

"Set Analysis" in Load Script

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

7 Replies
sunny_talwar
Not applicable

Re: "Set Analysis" in Load Script

Is this sum you are doing is per event?

blpetosa
Not applicable

Re: "Set Analysis" in Load Script

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
Not applicable

Re: "Set Analysis" in Load Script

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
Not applicable

Re: "Set Analysis" in Load Script

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
Not applicable

Re: "Set Analysis" in Load Script

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
Not applicable

Re: "Set Analysis" in Load Script

* generic load

blpetosa
Not applicable

Re: "Set Analysis" in Load Script

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.