0

View of table sorted by date within groups

I have a table that I want to create a view group my people, sorted by date of records by people and then sliced within each sorted group.

I have it working for a specific person but want to process the entire table and show the full results in a view.

here is what is working for a single person.

let h := PlayersHandle.Handle;
let d := ((select Rounds where Players.Handle = h) order by today() - 'Date Played');
let c := cnt(d);
let e := (slice(d, min(0, c - 20), 20) order by Differential);
slice(e, min(0, 1), 8)

13 replies

null
    • gyannes
    • 2 mths ago
    • Reported - view

    I have an idea on this.  I have a people table with an entry for each person. If I can read each record and process the code I have above looping through for each record until done.  I would change h to be let h:= People.Handle.

    How can i wrap the above code with process reading through the people table until done?

    • Fred
    • 2 mths ago
    • Reported - view

    I'm not sure what you are trying to do with the min() command in the slice.

    Line 4, you ask Ninox to find the minium between 0 and a formula. If the formula is below 0 then it will select the lower number. Then you ask Ninox to use that number in a slice. But an array can never have a starting number below 0, so I'm guessing that it just uses the first instance which is 0.

    Line 5, you ask to find the minimum of 0 or 1 so it will always be 0, so why not just use 0?

    Just to clarify, you want to find the first 8 Rounds records based on each Handle?

      • gyannes
      • 2 mths ago
      • Reported - view

       The first dice is getting the last 20 play dates.  Then out of the 20 I am take a dice to get the lowest 8 differentials.  In my example I am selecting records for a specific person.

      I want to do the same but for all people.

      The code I am using works perfectly for one person.  I need to review your comments on line 4 and 5.  I got that from another forum entry when I was researching how to dice. It works but may not be elegant.

      • gyannes
      • 2 mths ago
      • Reported - view

       Also, for line 4 the array will never be below 0 as the date field will never be a future date.

      Line 5 can probably be just 0.  I need to better understand that slice syntax.

      In any case my primary question is how can i read through the people table one record at a time and use that to group the second table by person, run my code and then get the next person run the code until done.  Displaying the 8 records for each person in a view.

      • gyannes
      • 2 mths ago
      • Reported - view

       one last note I removed the min on line 4 and 5 and used 0,20 and 0,8  that also worked so the forum example I used probably could have done the same.  I understand the slice syntax a bit more but need to review it more. The min is really not needed in my case.

      thanks for that input.

    • Fred
    • 2 mths ago
    • Reported - view
     said:
    I would change h to be let h:= People.Handle.

    Does that mean you have a reference field on the same table to People?

    If so then can you do:

    let d := People.Handle.Players.Rounds

    If you do it this way you don't have to do a select.

    You can also stack order bys. Try:

    let d := (((People.Handle.Players.Rounds) order by today()-'Date Played') order by Differential)

    You might want to play with the order of the order by to make sure it sorts the data correctly.

    If I have some baseball scores and I wanted to sort all records in the BOXSCORES table by city then by date it turns out I have to put the date sort first then the city sort.

    (((select BOXSCORES) order by Date) order by PARK.CITY)
    • Fred
    • 2 mths ago
    • Reported - view

    Try the following for your view element code:

    let allPeople := (select People);
let newArray := (select Rounds)[= -1];
for person in allPeople do
    let y := ((Handle.Players.Rounds order by today()-'Date Played') order by Differential);
    let b := slice(y, 0, 8);
    newArray := array(newArray, b);
end;
newArray

    It took me awhile to figure how to initialize the empty array on line 2. Not only does it have to be initialized to accept nid, but it has to match the exact table you are trying to add to.

      • gyannes
      • 2 mths ago
      • Reported - view

       thank you that is getting me closer.  I am going to play with it before i ask for more help or get stuck.  Can you explain line 2?  What is the [=-1] doing?

      • Fred
      • 2 mths ago
      • Reported - view

      The -1 tells Ninox to not find anything but initialize the variable to this table.

      It could also probably be rewritten to be:

      let newArray := Handle.Players.Rounds[= -1];

      No need for unnecessary selects.

      • gyannes
      • 2 mths ago
      • Reported - view

       ok after playing with this for some time I need some more input.  I update the code you provided to match my actual table names.  The code will follow.  I need to note that I still have two slice line for the following reason.  I first need to get the last 20 dates played so the selection is order by date.  Then out of that i need to get the lowest differential out of the 20.  Using the combined order by date then order by differential you provided only gets me the lowest differential for all play dates by player so if they played 30 times i need to bypass the first 10 dates played but the combo includes them.

      Having said that the code following gives me 8 returns for each player regardless of how many they played.  Some players only played 3 rounds but I get 8 rows returned.

      Also, all 8 rows for each player is the same data for the very last date they played.

      let allPlayers := (select Players);
      let newArray := (select Rounds)[= -1];
      for handle in allPlayers do
          let a := (allPlayers.handle.Rounds order by today() - 'Date Played');
          let b := (slice(a, 0, 20) order by Differential);
          let c := slice(b, 0, 8);
          newArray := array(newArray, c)
      end;
      newArray

      • Fred
      • 2 mths ago
      • Reported - view

      try this:

      let allPlayers := (select Players);
let newArray := (select Rounds)[= -1];
for player in allPlayers do
    let a := (player.handle.Rounds order by today() - 'Date Played');
    let b := (slice(a, 0, 20) order by Differential);
    let c := slice(b, 0, 8);
    newArray := array(newArray, c)
end;
newArray

      The main change is line 4, you had allPlayers in the variable. Which means you are always referencing the same 8 records for each player. You should reference the loop variable, which I changed to "player" to make it clear what you are referencing, so you are only looking at the rounds for each player.

      • gyannes
      • 2 mths ago
      • Reported - view

       great I got it to work with one change.  Instead of player in 3 and 4 i used handle.  And in 4 handle.rounds instead of player.handle.rounds 

       

      thanks so much for your help

      • Fred
      • 2 mths ago
      • Reported - view

      When you get a chance, please mark the post answered.

Login to reply

Content aside

  • Status Answered
  • 2 mths agoLast active
  • 13Replies
  • 101Views
  • 2 Following