topic Re: Set Analysis To Look at Data In Next Year in QlikView

Set Analysis To Look at Data In Next Year

dclark0699 — Wed, 29 Oct 2014 23:12:06 GMT

With this data:

PLAYERS:

LOAD * INLINE [

playerID, HR, 2B, 3B, yearID

bautijo02, 54, 35, 3, 2010

bautijo02, 43, 24, 2, 2011

beltrad01, 48, 32, 0, 2004

beltrca01, 41, 38, 1, 2006

berkmla01, 42, 35, 2, 2002

berkmla01, 45, 29, 0, 2006

bondsba01, 73, 32, 2, 2001

bondsba01, 46, 31, 2, 2002

bondsba01, 45, 22, 1, 2003

bondsba01, 45, 27, 3, 2004

];

Is it possible to set up set analysis to return a list of players who hit at least 40 HR and then the following calendar year also hit at least 40 HR?

The expected results being

bautijo02

bondsba01

And then if that is possible, can you also say, within four years of hitting at least 40 HR, they hit at least 40 HR again?

The expected results being

bautijo02

bondsba01

berkmla01

Thanks for any advice you can provide!

Re: Set Analysis To Look at Data In Next Year

anbu1984 — Thu, 30 Oct 2014 01:58:03 GMT

In Script,

PLAYERS:

LOAD * INLINE [

playerID, HR, 2B, 3B, yearID

bautijo02, 54, 35, 3, 2010

bautijo02, 43, 24, 2, 2011

beltrad01, 48, 32, 0, 2004

beltrca01, 41, 38, 1, 2006

berkmla01, 42, 35, 2, 2002

berkmla01, 45, 29, 0, 2006

bondsba01, 73, 32, 2, 2001

bondsba01, 46, 31, 2, 2002

bondsba01, 45, 22, 1, 2003

bondsba01, 45, 27, 3, 2004

];

NoConcatenate

Final:

Load *,If(playerID = Previous(playerID) And yearID = Previous(yearID)+1 And HR > 40 And Previous(HR) > 40, 1) As Flag

Resident PLAYERS Order by playerID,yearID;

Drop Table PLAYERS;

Based on Flag=1, you can retrieve playerID

Re: Set Analysis To Look at Data In Next Year

jagan — Thu, 30 Oct 2014 02:14:32 GMT

Hi,

Try like this

Temp:

LOAD * INLINE [

playerID, HR, 2B, 3B, yearID

bautijo02, 54, 35, 3, 2010

bautijo02, 43, 24, 2, 2011

beltrad01, 48, 32, 0, 2004

beltrca01, 41, 38, 1, 2006

berkmla01, 42, 35, 2, 2002

berkmla01, 45, 29, 0, 2006

bondsba01, 73, 32, 2, 2001

bondsba01, 46, 31, 2, 2002

bondsba01, 45, 22, 1, 2003

bondsba01, 45, 27, 3, 2004

];

Players:

LOAD

If(Peek('playerID') = playerID AND Peek('HR') >= 40 AND HR >= 40, 1, 0) AS Flag

FROM Temp

ORDER BY playerID, yearID;

Drop Table Temp;

Now if you want to see a list of players who hit at least 40 HR and then the following calendar year also hit at least 40 HR just select Flag = 1 then you will see the players.

Hope this helps you.

Regards,

Jagan.

Re: Set Analysis To Look at Data In Next Year

dclark0699 — Thu, 30 Oct 2014 09:36:11 GMT

While, yes this would solve this particular example, I'm looking for a way to do it without flags in the load script. For my particular case, I need it to be more dynamic than a flag on the script.

For example, I might place an input box where the user can say 1 year between, 4 years between, 6 years between etc. And I like to be able to do it against different fields. It would require potentially infinite flags.

Re: Set Analysis To Look at Data In Next Year

dclark0699 — Thu, 30 Oct 2014 09:36:23 GMT

While, yes this would solve this particular example, I'm looking for a way to do it without flags in the load script. For my particular case, I need it to be more dynamic than a flag on the script.

Re: Set Analysis To Look at Data In Next Year

Thu, 30 Oct 2014 09:58:32 GMT

If you want to do it in Front end.

The Create a List box, with calculated expression as : =Class(HR,5)

Also you can add this in Straight Table Expression with PlayerID and YearID as dimensions to create bucket.

Otherwise in script level use Interval Match function to create a customized bucket and map your data with that.

Hope this was helpful.

Thanks,

Singh

Re: Set Analysis To Look at Data In Next Year

dclark0699 — Thu, 30 Oct 2014 11:36:39 GMT

This is a very interesting function that I've never used before, but I don't see how it will calculate the number of years between two 40 HR seasons. It classifies the number of HRs into buckets x wide instead of checking x years into the future

Re: Set Analysis To Look at Data In Next Year

Thu, 30 Oct 2014 12:04:32 GMT

Can you please explain more about this scenario based on YearID. My observation is that the expected results in the second case are where we have more than 1 entry for PlayerID with atleast 40 as HR.

Can you correct me, if I have misunderstood the requirement?

Re: Set Analysis To Look at Data In Next Year

dclark0699 — Thu, 30 Oct 2014 12:08:53 GMT

I'm looking at how long the gap was between yearID. In the second example, I was testing who had hit 40+ HR within 4 years of hitting 40+ HR

bautijo02 2011-2010 = 1

bondsba01 2004-2003 = 1, 2003-2002 = 1, 2002-2001 = 1

berkmla01 2006-2002 = 4

So If I changed an input field from 4 to 3 it would return only

bautijo02 2011-2010 = 1

bondsba01 2004-2003 = 1, 2003-2002 = 1, 2002-2001 = 1

Because it took berkmla01 4 years to hit 40+ HR again

Does that make sense?

Re: Set Analysis To Look at Data In Next Year

Thu, 30 Oct 2014 13:50:50 GMT

It does make sense now,

Try below steps:

Script Change:

table1:

LOAD

playerID,

max(yearID)-min(yearID) as YearFlag

Resident PLAYERS

group by playerID;

FrontEnd:

Create a Straight Table

Dimensions: playerID,yearID and HR

Expression: only({<YearFlag= {"<=$(=vYearRange)"}>}playerID) and Hide it this expression in Presentation Tab.

Filters:

vYearRange is a input variable, where you will input a number.

=Class(HR,5) as a list box to filter the HR thing

This is what I could make up, hope this will be helpful.

Thanks,

Singh

Re: Set Analysis To Look at Data In Next Year

dclark0699 — Thu, 30 Oct 2014 21:06:57 GMT

I'm really trying at all costs to avoid the load script, because in this case it's HRs, but I'd like to be dynamic enough to use on any column.

For example, the same solution would have to work on 2Bs and 3Bs without creating a flag column for all of them

Re: Set Analysis To Look at Data In Next Year

Fri, 31 Oct 2014 05:00:38 GMT

I do understand what you mean. The another way is to use functions like Above() in the expressions.

Like your expression can be yearID-above(yearID), but i am still trying to make it more dynamic and respect the input field values and HR bucket selection with this.

You can also try playing around with inter-record functions.

Thanks,

Singh