locked
Who helps a German Xlinq Beginner ? (group,sum,avg) RRS feed

  • Question

  • I am happy to show some fields from this Sample file:
    http://www.traum-touren.de/develop/volsi.xml

    But i need moore:     ->Group by DAT, Sum of KM (for Group), Average of AV (for Group)
    Who helps to enlarge this Snippet:
            Dim XMLele As XElement = XElement.Load("http://www.traum-touren.de/develop/volsi.xml")
            Dim SEL = From MD In XMLele.<DAY>.<XX> Select MD.<DAT>.Value, _
               MD.<KM>.Value, MD.<AVG>.Value, MD.<CITY>.Value
            For results = 0 To SEL.Count - 1
                Console.WriteLine(SEL.ElementAt(results))
            Next

    This is my manually calculatet (wish)result:
    DATE = 2008-06-26 TOTALKM = 517,93 AVG = 172,64
    DATE = 2008-06-27 TOTALKM =  65,25 AVG =  63,35
    DATE = 2008-06-28 TOTALKM = 118,34 AVG =  39,45

    Thanks from Germany

    Andy
    Thursday, December 18, 2008 10:26 PM

Answers

  • Hello Andy

     

    below is the query:

     

    Code Snippet

    XElement XMLele = XElement.Load("http://www.traum-touren.de/develop/volsi.xml");

    //.Elements("DAY").Elements("XX")

    var resulSet = from md in XMLele.Elements("DAY").Elements("XX")

    select new { KM = md.Element("KM"), Date = md.Element("DAT") };

    var groupstatements = from k in resulSet

    group k.KM.Value by k.Date.Value

    into g

    select new

    {

    DATE = g.Key,

    TOTALKM = g.Sum(k => Int32.Parse(k, NumberStyles.AllowThousands)),

    AVG = g.Sum(k => Int32.Parse(k, NumberStyles.AllowThousands)) / g.Count(),

    };

     

     

     

    foreach (var item in groupstatements)

    {

    MessageBox.Show(item.DATE + "_" + item.TOTALKM.ToString()

    +"_" + item.AVG);

    }

     

     

    I have calculated average by dividing total KMs. by no. of records, You can calculate the way you want.

     

     

    Friday, December 19, 2008 6:14 AM
  • Here is an example showing grouping with VB.NET:

    Code Snippet

            Dim de As New CultureInfo("de-DE")
            Dim url As String = "http://www.traum-touren.de/develop/volsi.xml"
            Dim vol As XElement = XElement.Load(url)
            Dim query = _
            From xx In vol.<DAY>.<XX> _
            Group xx By dat = xx.<DAT>.Value Into datGroup = Group, TotalKm = Sum(Double.Parse(xx.<KM>.Value, de)), _
            Average = Average(Double.Parse(xx.<AVG>.Value, de))

            For Each item In query
                Console.WriteLine("DATE = {0}, TOTALKM = {1} AVERAGE = {2}", item.dat, item.TotalKm, item.Average)
            Next

     

     

    The computed results however differ from your wish result, the result of the above code is

     

    DATE = 2008-06-26, TOTALKM = 517,93 AVERAGE = 89,2866666666667
    DATE = 2008-06-27, TOTALKM = 65,25 AVERAGE = 63,35
    DATE = 2008-06-28, TOTALKM = 258,01 AVERAGE = 63,7275

     

    I am not sure whether I have misunderstood what you want to group by or whether you miscalculated the results you posted.

    Friday, December 19, 2008 1:00 PM
  • I think Martin answered your specific question. As for the question from your subject line, I'll make a gratuitious plug. You can get the German translation of LINQ in Action at http://www.amazon.de/LINQ-im-Einsatz-Fabrice-Marguerie/dp/3446414290.

     

    Jim Wooley

    www.ThinqLinq.com

    Friday, December 19, 2008 7:39 PM
  • I have had another look at the sample data you provided and while it still does not completely make sense to me I think you can to apply the Average function to the KM elements so here is a slightly adapted sample:

    Code Snippet

            Dim de As New CultureInfo("de-DE")
            Dim url As String = "http://www.traum-touren.de/develop/volsi.xml"
            Dim vol As XElement = XElement.Load(url)
            Dim query = _
            From xx In vol.<DAY>.<XX> _
            Group xx By dat = xx.<DAT>.Value Into datGroup = Group, TotalKm = Sum(Double.Parse(xx.<KM>.Value, de)), _
            Average = Average(Double.Parse(xx.<KM>.Value, de))

            For Each item In query
                Console.WriteLine("DATE = {0}, TOTALKM = {1} AVERAGE = {2}", item.dat, item.TotalKm, item.Average)
            Next

     

     

    Saturday, December 20, 2008 4:53 PM

All replies

  • Hello Andy

     

    below is the query:

     

    Code Snippet

    XElement XMLele = XElement.Load("http://www.traum-touren.de/develop/volsi.xml");

    //.Elements("DAY").Elements("XX")

    var resulSet = from md in XMLele.Elements("DAY").Elements("XX")

    select new { KM = md.Element("KM"), Date = md.Element("DAT") };

    var groupstatements = from k in resulSet

    group k.KM.Value by k.Date.Value

    into g

    select new

    {

    DATE = g.Key,

    TOTALKM = g.Sum(k => Int32.Parse(k, NumberStyles.AllowThousands)),

    AVG = g.Sum(k => Int32.Parse(k, NumberStyles.AllowThousands)) / g.Count(),

    };

     

     

     

    foreach (var item in groupstatements)

    {

    MessageBox.Show(item.DATE + "_" + item.TOTALKM.ToString()

    +"_" + item.AVG);

    }

     

     

    I have calculated average by dividing total KMs. by no. of records, You can calculate the way you want.

     

     

    Friday, December 19, 2008 6:14 AM
  • Here is an example showing grouping with VB.NET:

    Code Snippet

            Dim de As New CultureInfo("de-DE")
            Dim url As String = "http://www.traum-touren.de/develop/volsi.xml"
            Dim vol As XElement = XElement.Load(url)
            Dim query = _
            From xx In vol.<DAY>.<XX> _
            Group xx By dat = xx.<DAT>.Value Into datGroup = Group, TotalKm = Sum(Double.Parse(xx.<KM>.Value, de)), _
            Average = Average(Double.Parse(xx.<AVG>.Value, de))

            For Each item In query
                Console.WriteLine("DATE = {0}, TOTALKM = {1} AVERAGE = {2}", item.dat, item.TotalKm, item.Average)
            Next

     

     

    The computed results however differ from your wish result, the result of the above code is

     

    DATE = 2008-06-26, TOTALKM = 517,93 AVERAGE = 89,2866666666667
    DATE = 2008-06-27, TOTALKM = 65,25 AVERAGE = 63,35
    DATE = 2008-06-28, TOTALKM = 258,01 AVERAGE = 63,7275

     

    I am not sure whether I have misunderstood what you want to group by or whether you miscalculated the results you posted.

    Friday, December 19, 2008 1:00 PM
  • I think Martin answered your specific question. As for the question from your subject line, I'll make a gratuitious plug. You can get the German translation of LINQ in Action at http://www.amazon.de/LINQ-im-Einsatz-Fabrice-Marguerie/dp/3446414290.

     

    Jim Wooley

    www.ThinqLinq.com

    Friday, December 19, 2008 7:39 PM
  • Thanks to

    Manish - i had problems to convert yours in vb
    Martin  - VB is Sympa, i try this tomorrow
    Jim      - now i go in the City to get your book..

    thanks to all over the Ocean... the theme LINQ is to new here...

    Andy



    Saturday, December 20, 2008 10:29 AM
  • I have had another look at the sample data you provided and while it still does not completely make sense to me I think you can to apply the Average function to the KM elements so here is a slightly adapted sample:

    Code Snippet

            Dim de As New CultureInfo("de-DE")
            Dim url As String = "http://www.traum-touren.de/develop/volsi.xml"
            Dim vol As XElement = XElement.Load(url)
            Dim query = _
            From xx In vol.<DAY>.<XX> _
            Group xx By dat = xx.<DAT>.Value Into datGroup = Group, TotalKm = Sum(Double.Parse(xx.<KM>.Value, de)), _
            Average = Average(Double.Parse(xx.<KM>.Value, de))

            For Each item In query
                Console.WriteLine("DATE = {0}, TOTALKM = {1} AVERAGE = {2}", item.dat, item.TotalKm, item.Average)
            Next

     

     

    Saturday, December 20, 2008 4:53 PM
  • this sample works fine, thanks.
    The book: LINQ in Action will help also...

    Andy

    http://maps.google.de/maps?f=q&hl=de&geocode=&q=http:%2F%2Fwww.traum-touren.de%2Fsonst%2FAndy_at_home.kml&g=Neuer+Trieb+3,+61206+W%C3%B6llstadt&ie=UTF8&ll=50.276417,8.767188&spn=0.002324,0.003428&t=h&z=18


    Monday, December 22, 2008 5:28 PM