SolvedPrivate

Vertical Sum For Top 3

Posted on 2013-11-23
6
39 Views
Last Modified: 2016-02-10
Hi Experts!

Thanks for reading this.
This is a challenging one for me; if I could give 1000+ I would.
Supposed you've a table with this content:
Qid        Value
---------- -----
1            0
2            2
3            1
4            4
5            0
6            0
7            1
8            2
9            0
10           4

Open in new window

Here's how to use the content; you want to get A, B, C, D, and E:
A = (Qid of 1) + (Qid of 2) = 0 + 2 = 2
B = (Qid of 3) + (Qid of 4) = 1 + 4 = 5
C = (Qid of 5) + (Qid of 6) = 0 + 0 = 0
D = (Qid of 7) + (Qid of 8) = 1 + 2 = 3
E = (Qid of 9) + (Qid of 10) = 0 + 4 = 4

Open in new window

Ultimately you would like to get the top 3 values.
So, they are: B (5), E (4), and D (3)

The actual logic is like this:
questions.PNG
We'll need to do this in LINQ; here's the pseudo code:
Q q1  = new Q { Qid=1 , Value = "0" };
Q q2  = new Q { Qid=2 , Value = "2" };
Q q3  = new Q { Qid=3 , Value = "1" };
Q q4  = new Q { Qid=4 , Value = "4" };
Q q5  = new Q { Qid=5 , Value = "0" };
Q q6  = new Q { Qid=6 , Value = "0" };
Q q7  = new Q { Qid=7 , Value = "1" };
Q q8  = new Q { Qid=8 , Value = "2" };
Q q9  = new Q { Qid=9 , Value = "0" };
Q q10 = new Q { Qid=10, Value = "4" };


List<Q> Qs = new List<Q> { q1, q2, q3, q4, q5, q6, q7, q8, q9, q10};

var results = from q in Qs ................
              select new
			  {
			  A =
			  B =
			  C =
			  D =
			  E =
			  };

var Top3 = from r in results ..............

Open in new window


Any ideas?
0
Comment
Question by:allanau20
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39671321
Try this:
            List<Q> list = new List<Q>
            {
                new Q { Qid = 1, Value = 0},
                new Q { Qid = 2, Value = 2},
                new Q { Qid = 3, Value = 1},
                new Q { Qid = 4, Value = 4},
                new Q { Qid = 5, Value = 0},
                new Q { Qid = 6, Value = 0},
                new Q { Qid = 7, Value = 1},
                new Q { Qid = 8, Value = 2},
                new Q { Qid = 9, Value = 0},
                new Q { Qid = 10, Value = 4}
            };

            var query = from x in list
                        join y in list on (x.Qid + 1) equals y.Qid
                        where x.Qid % 2 == 1
                        select (x.Value + y.Value);

            var results = (from x in query
                           orderby x descending
                           select x).Take(3);

Open in new window

0
 
LVL 5

Author Comment

by:allanau20
ID: 39671940
Thanks pretty clever carl_tawn. The top 3 is correct; but we're unable to determine from which of 'A-E' is it associated with. In our example, the top 3 are: B = 5, E = 4, and D = 3

Can you please take a look at my 'query'? Trying to add three Qids to make this:
questionA= Qid1 + Qid17 + Qid33, B=Qid2 + Qid18 + Qid34, all the way to P.
    List<Q> list = new List<Q>
{
    new Q { Qid = 1, Value = 0},
    new Q { Qid = 2, Value = 2},
    new Q { Qid = 3, Value = 1},
    new Q { Qid = 4, Value = 4},
    new Q { Qid = 5, Value = 0},
    new Q { Qid = 6, Value = 0},
    new Q { Qid = 7, Value = 1},
    new Q { Qid = 8, Value = 2},
    new Q { Qid = 9, Value = 0},
    new Q { Qid = 10, Value = 4},
    new Q { Qid = 11, Value = 0},
    new Q { Qid = 12, Value = 2},
    new Q { Qid = 13, Value = 1},
    new Q { Qid = 14, Value = 4},
    new Q { Qid = 15, Value = 0},
    new Q { Qid = 16, Value = 0},
    new Q { Qid = 17, Value = 1},
    new Q { Qid = 18, Value = 2},
    new Q { Qid = 19, Value = 0},
    new Q { Qid = 20, Value = 4},
    new Q { Qid = 21, Value = 0},
    new Q { Qid = 22, Value = 2},
    new Q { Qid = 23, Value = 1},
    new Q { Qid = 24, Value = 4},
    new Q { Qid = 25, Value = 0},
    new Q { Qid = 26, Value = 0},
    new Q { Qid = 27, Value = 1},
    new Q { Qid = 28, Value = 2},
    new Q { Qid = 29, Value = 0},
    new Q { Qid = 30, Value = 4},
    new Q { Qid = 31, Value = 0},
    new Q { Qid = 32, Value = 2},
    new Q { Qid = 33, Value = 1},
    new Q { Qid = 34, Value = 4},
    new Q { Qid = 35, Value = 0},
    new Q { Qid = 36, Value = 0},
    new Q { Qid = 37, Value = 1},
    new Q { Qid = 38, Value = 2},
    new Q { Qid = 39, Value = 0},
    new Q { Qid = 40, Value = 4},
    new Q { Qid = 41, Value = 0},
    new Q { Qid = 42, Value = 2},
    new Q { Qid = 43, Value = 1},
    new Q { Qid = 44, Value = 4},
    new Q { Qid = 45, Value = 0},
    new Q { Qid = 46, Value = 0},
    new Q { Qid = 47, Value = 1},
    new Q { Qid = 48, Value = 2}
};

    var query = from x in list
                join y in list on (x.Qid + 16) equals y.Qid
                join z in list on (x.Qid + 32) equals z.Qid
                select (x.Value + y.Value + z.Value);

    var results = (from x in query
                   orderby x descending
                   select x).Take(3);

Open in new window


Is there a way to tell from which logic (A-P) the top 3 came from?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39672832
This should do what you want:
var query = (from x in list
            join y in list on (x.Qid + 16) equals y.Qid
            join z in list on (x.Qid + 32) equals z.Qid
            where x.Qid <= 16
            select x.Value + y.Value + z.Value)
    .Select((item, index) => new { Letter = (char)(65 + index), Value = (int)item });

var results = (from x in query
                      orderby x.Value descending
                       select x).Take(3);

foreach(var item in results)
{
    Console.WriteLine(item.Letter + "=" + item.Value);
}

Open in new window

0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 5

Author Comment

by:allanau20
ID: 39673627
Thanks carl_tawn; came across this in results:

error
LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[<>f__AnonymousType5`2[System.Char,System.Int32]] Select[Int32,<>f__AnonymousType5`2](System.Linq.IQueryable`1[System.Int32], System.Linq.Expressions.Expression`1[System.Func`3[System.Int32,System.Int32,<>f__AnonymousType5`2[System.Char,System.Int32]]])' method, and this method cannot be translated into a store expression.

The example above is using local values, but what I'm doing is querying two tables in a database:
var answered = from a in db.tbl01
                join v in db.tbl01
                on a.ScaleID equals v.tScaleID
                where a.UserID == myUser.UserID
                select new
                {
                    Qid = a.QuestionID,
                    Value = v.ScaleValue
                };


var query = (from x in answered
                join y in answered on (x.Qid + 16) equals y.Qid
                join z in answered on (x.Qid + 32) equals z.Qid
                where x.Qid <= 16
        select x.Value + y.Value + z.Value)
        .Select((item, index) => new { Letter = (char)(65 + index), Value = item });


var results = (from x in query
                orderby x.Value descending
                select x).Take(3);

Open in new window


Qid and Value are Int data type.

I tried and cannot get it to work. Thanks again for your help!
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 39674217
You need to detach your result set from the Entity view of the model. Assuming you still have your Q class kicking around, you can change the line:
var answered = from a in db.tbl01
                join v in db.tbl01
                on a.ScaleID equals v.tScaleID
                where a.UserID == myUser.UserID
                select new
                {
                    Qid = a.QuestionID,
                    Value = v.ScaleValue
                };

Open in new window

To:
List<Q> answered = (from a in db.tbl01
                join v in db.tbl01
                on a.ScaleID equals v.tScaleID
                where a.UserID == myUser.UserID
                select new Q
                {
                    Qid = a.QuestionID,
                    Value = v.ScaleValue
                }).ToList<Q>();

Open in new window

And the rest should then work as before.
0
 
LVL 5

Author Closing Comment

by:allanau20
ID: 39676311
Thank you carl_tawn; I saved time and learned something new from you!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question