topic Re: FirstSortedValue from two sources in QlikView

FirstSortedValue from two sources

Anonymous — Fri, 05 Sep 2014 20:35:54 GMT

I have a formula that uses the FirstSortedValue function that works fine as long as there is only one house that the iHeadMort comes from, but if it comes from two houses then I need some way to add the two amounts together.

=FirstSortedValue(iHeadMort,Mort_dt)

I tried something like this, but it did not work:

=FirstSortedValue(SUM(iHeadMort),Mort_dt)

Any ideas will be greatly appreciated.

Re: FirstSortedValue from two sources

JonnyPoole — Fri, 05 Sep 2014 20:49:46 GMT

Is this for a chart ?

You can use the same chart aggregation syntax with firstsortedvalue. Assuming House is a field in the data model try this:

FirstSortedValue(TOTAL <House> iHeadMort ,Mort_dt)

it will bring the first value of iHeadMort within each grouping of House.

Re: FirstSortedValue from two sources

Anonymous — Fri, 05 Sep 2014 21:00:51 GMT

Jonathan,

It is for a table. That sounds like it would work, but it didn't. I think its on the right track though.

Re: FirstSortedValue from two sources

Fri, 05 Sep 2014 21:05:07 GMT

If it is for a back end table I would try a Group by.

Thank you

Suraj

Re: FirstSortedValue from two sources

JonnyPoole — Fri, 05 Sep 2014 21:47:15 GMT

OK. Can you share a sample qvw ?

Re: FirstSortedValue from two sources

IAMDV — Sat, 06 Sep 2014 11:53:01 GMT

Hi Bradley,

You need something like this:

=FirstSortedValue(iHeadMort, Aggr(SUM(Mort_dt),iHeadMort))

FirstSortedValue() functions supports nested aggregation using the Aggr() function. Also, I'd recommend you to watch below video post on my blog.

http://qlikshare.com/tag/firstsortedvalue/

Cheers,

www.QlikShare.com

Re: Re: FirstSortedValue from two sources

Anonymous — Mon, 08 Sep 2014 13:42:36 GMT

Here is the table and a screenshot. I tried the Aggr formula from Deepak with no success. I think we are close to the solution and this is the last obstacle before this table can be published. Please help.

Re: FirstSortedValue from two sources

JonnyPoole — Mon, 08 Sep 2014 14:23:29 GMT

Bradley - can you tell me which field is your 'house' field ? Also I assume the expression that is incorrect is 'Week 1 Mort %' ?

Also the above approach isn't relevant because you need a combination of SUM and firstsortedvalue. The solution will likely be akin to what Deepak wrote below ...just need some more details of your model.

Re: FirstSortedValue from two sources

Anonymous — Mon, 08 Sep 2014 14:28:04 GMT

The field is house_# and yes Week 1 Mort % is the incorrect expression.

Re: Re: FirstSortedValue from two sources

IAMDV — Mon, 08 Sep 2014 15:43:55 GMT

Hi Bradley,

I assume that you wanted to use this expression in the Pivot Table. If yes, have you tried the following expression:

FirstSortedValue(House_#, Aggr(SUM(Mort_dt), House_#, sSvcman_cd, Project_#, Group_Name, Hatch_Date, Place_Date, Breed, Condition_Code, Breeder_Group))

PS: Add one more missing dimension in the above expression which is there in your chart [S E X]. If I include in the expression it requires moderation hence I'm writing this message seperately.

It works for me!

If you need to use the expression elsewhere please let me know which chart/sheet.

Cheers,

www.QlikShare.com

Re: FirstSortedValue from two sources

Anonymous — Mon, 08 Sep 2014 16:47:23 GMT

Deepak,

So were you able to open the test file and use this expression to return the Week 1 Mort % for the group cd: 4120LR14 that was currently missing?

Re: FirstSortedValue from two sources

JonnyPoole — Mon, 08 Sep 2014 20:26:12 GMT

Hi Bradley, I looked at your chart but based on your data model i see an issue with the following description:

"I have a formula that uses the FirstSortedValue function that works fine as long as there is only one house that the iHeadMort comes from, but if it comes from two houses then I need some way to add the two amounts together."

iHeadMort comes from the GroupMortality table and the numbers are associateed to the rest of the data model on 'Group Key ID' .

'Group Key ID' will associate to multiple values of 'Breeder Group' through a linked table .From there, each combination will associated to one or more records in the 'Group Placements' table. House_# is in this Group placements table and there may be several records for each house and also several houses.

So basically iHeadMort seems to be reported at a higher level than House_# in a separate table, so if I have an iHeadMort value of 100 , i'm not sure i understand how to come up with broken down value for each house. Even if you are only asking for the oldest value based on Mort_dt , that single record from the GroupMortality table may associate to multiple house_#'s from the 'Group Placements' table

Its sort of like reporting the population of a continent , and then asking qlik for an expression that calculates the country... you can't necause the data doesn't have the populations reported at the country level ...and so the question only works if there is only 1 country in a continent.

I'm not sure what your source data says but you may need to create a data model that has both house and iHeadMort in the same table or at least iHeadMort reported at the same or lower level of granularity as 'house_#'.

Am i way off ? i spent a bit of time looking at it and i'm a bit cross eyed.

Re: Re: FirstSortedValue from two sources

IAMDV — Tue, 09 Sep 2014 08:40:00 GMT

Hi,

I'm getting results for [Week 1 Mort%]. But I'm not sure if they are expected results. See the attached screenshot and following expression. I can see the values for [Group Cd] field, however for the rows 4120LR14 you're getting same numbers even after aggregating the data hence you'll see two NULL values. I've checked these values and both of them have same dimension values and same expression result values, hence FirstSortedValue() with Aggr() function still returns NULL values for these two records. But rest everything seems to be working. Let me know if this what you need?

Expression:

FirstSortedValue(House_#, Aggr( SUM(Mort_dt), House_#, sSvcman_cd, Project_#, Group_Name, Hatch_Date, Place_Date, Breed, Sex, Condition_Code, Breeder_Group))

SUM(total <House_#, sSvcman_cd, Project_#, Group_Name, Hatch_Date, Place_Date, Breed, Sex, Condition_Code, Breeder_Group> Head_Start)

Cheers,

www.QlikShare.com

Re: FirstSortedValue from two sources

Anonymous — Tue, 09 Sep 2014 12:45:04 GMT

Thanks guys for your help. I hope to be just like you when I grow up . Thank you Jonathan for your time spent digging into the root of the problem, and thank you Deppak for never giving up on the correct answer. I will look at another way to load the information if this becomes a big problem. For now it seems that this problem is not correctable with the current state of the data. Again, I thank you for your time and wisdom on this problem.

Re: FirstSortedValue from two sources

JonnyPoole — Tue, 09 Sep 2014 12:51:39 GMT

Thank you Brad.

Re: Re: FirstSortedValue from two sources

IAMDV — Tue, 09 Sep 2014 13:10:30 GMT

That's great Brad. In fact, I like working on these kind of issues. I wish you good luck!

Jonathan - You're one of few people on community, who cares to give not just the correct answer but also helpful & friendly response with clear explanation instead of just aiming at the points. Thank you for your all contributions on the community.

Cheers,

www.QlikShare.com

Re: FirstSortedValue from two sources

JonnyPoole — Tue, 09 Sep 2014 13:29:08 GMT

cheers deepak! i can see Bradley put together some effort to create a sample so the least i can do is the same. i thought i knew a fair amount of QV prior to 'getting active' on community but the range of solutions from yourself and others that i see on this forum honestly has me back to learning things every day which i did not expect... but that is the nature of qlikview so i should not be surprised

Re: FirstSortedValue from two sources

Anonymous — Tue, 09 Sep 2014 13:33:49 GMT

Guys,

I just spoke with one of my coworkers here and showed them Jonathan's explanation and he said that I am not looking to group from House_# that is in the placement table, but from MortHouse that comes from the mortality table. would this help make a difference in the levels Jonathan mentioned?

Re: FirstSortedValue from two sources

JonnyPoole — Tue, 09 Sep 2014 13:38:49 GMT

It definitely should. Thats the same table where the other 2 fields come from so we should be good on the granularity... stay tuned

Re: FirstSortedValue from two sources

Anonymous — Wed, 10 Sep 2014 13:30:30 GMT

So what I need now is some way to add the amount from MortHouse 1 & 2 together but still only retrieve the FirstSortedValue for each. Do I need some sort of Aggr function as Deepak mentioned earlier, or is there another way to total these?