Extracting multiple rows at a time

[maretec]

I have a table where loan payments are recorded.

Each borrower can make one, 2, 3 or more payments during the month to complete their monthly loan payment.

For example, if the monthly loan payment is $956.25, the borrower can make 4 payments during the month. I'm going to pick random amounts, say $256.25, $200.00, $350.00, and $150.00. These 4 payments complete the monthly loan payment of $956.25.

The [Monthly Payment] column of the table sums the payments made by a borrower during a month. In the example above, the [Monthly Payment] column will have the following values: $256.25, $456.25, $806.25, and $956.25

I want to extract the rows whose [Monthly Payment] has the highest amount for each loan and paste them into another table.

In the image below, I have 3 loans: d88bc4a9, c672d16b and 0c567ba0.

I want to extract rows 18, 19 and 21.

I've been able to extract one row at a time. I've tried "Actions" to execute a loop to extract all rows from a month, but I don't find the right way to do it.

Any advice will be welcome.

[dbaum · Accepted solution]

There are a few different techniques depending on your data structure vis à vis any other referenced tables.

Here's one approach, based on using the row filter property for a slice of the Loan Details table.

[LoanDetails ID] = MAXROW("LoanDetails", "Monthly Payment", AND([Loan ID] = [_THISROW].[Loan ID], [First Day] = [_THISROW].[First Day]))

[maretec]

Thank you dbaum.

Your expression filters one row.  I've been able to extract one row at a time, too. I like your expression because is shorter than the mine:

ANY(SELECT(Loans Details[LoanDetails ID],[Monthly Payment]=MAX(SELECT(Loans Details[Monthly Payment],AND([_THISROW].[Date]=[Last Day],[_THISROW].[Picked Loan]=[Loan])))))

Both expressions work fine to filter one loan/row.  I have to do the same for all the loans of the month (this is the part where I'm stuck).  So I have to repeat the process. I am trying via "Actions".

There are 2 payment periods:

1. From the 16th of a month to the 15th of the next month (e.g., June 16th - July 15,  2023).
2. From the 1st of a month to the end of that month (e.g., July 1st - July 31,  2023).

But for the sake of simplicity, I will only refer to the period from the 1st of a month to the end of that month.

Please keep in mind that the objective is to extract only the rows that have the highest [Monthly Payment] for each loan during a month. 

The table below explains how do I extract one row. 

Now I have to repeat the process and extract other loans/rows.  I think it's possible via "Actions":

1. Using the list of loans in the [LoansByDate] column, I can subtract the loans that have already been extracted in order to have a "remaining loans" list. This means that I have to create a list of "extracted loans".
2. Having the list of remaining loans, I can pick up the first loan of that list and repeat the process, applying the expressions described in the table above.
3. Steps 1 and 2 are repeated until there's no loan in the "remaining loans" list.

I've reviewed these examples of looping:

• Looping with Actions (Sample App BUILD Video)
• Add any number of rows from any row within a table

I created some actions, but without success to make the loop. I will appreciate any help.

I know there are more than one way to do things in AppSheet, so I keep my mind open to "listen" to other approaches.

[dbaum]

---

@maretec wrote:

Your expression filters one row.

---

Presumably, that depends on where you deploy the expression. Did you use it in a slice's row filter property as I indicated?

---

[maretec]

I read your first post again  and I realized I didn't see that I have to create a slice.  Thank you for the observation.  Your suggestion is working . . . 😃

I made some adjustments to the expression:
[LoanDetails ID] = MAXROW("Loans Details", "Monthly Payment", AND([Loan] = [_THISROW].[Loan], [Last Day] = [_THISROW].[Last Day])) 

The issue with this solution is that I have to show the data in Looker Studio and a slice is just a view, not a table. The goal is to have a history of all payments in one table so users can "play" with the data using Looker Studio.

I know I can download the data to a CSV file and present it in Looker Studio, but each download is a different file, so Looker Studio will just see the first CSV. I want to avoid manual tasks for the users.  The process must be automated and transparent for the users.

Based on your experience, what do you recommend?

[dbaum]

I don't follow everything you're trying to do and, regardless, have never needed to represent data from an AppSheet app in Looker. Nonetheless, now that you have the relevant rows reliably compiled in the slice, perhaps you can use one of the techniques you were already using to copy rows from the slice to another table. You could probably automate that--either on a schedule or every time a new LoanDetails row is added.

[maretec]

Ok. I'll try to find out a way to automate it.

Thank you.