?
SolvedPrivate

Vertical Sum For Top 3

Posted on 2013-11-23
6
Medium Priority
?
44 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 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