Trying to remove duplicate for a <<START>> .... <<END>> Loop?

[CaptainEon]

I am trying to removed duplicates for a Loop so I can Loop inside the Loop!

<<START: UNIQUE(ORDERBY(FILTER(Items, AND([Building ID] = [_THISROW].[Building ID],  [Item] <> “None)), [Location], FALSE))>>

Another Loop below

<<START: ORDERBY(FILTER(Items, AND([Building ID] = [_THISROW].[Building ID], [Location] =  [_THISROW-1].[Location],  [Item] <> “None”)), [Location], FALSE)>>

<<END>

<<END>>

Without the inner loop it works.

Once I put the inner loop in it duplicates as if ignoring the UNIQUE() function

[Steve · Accepted solution]

Whoops! I left something out. Try this instead:

<<Start:
  ORDERBY(
    FILTER(
      "Items",
      AND(
        ([Building ID] = [_THISROW].[Building ID]),
        ([Item] <> “None"),
        (
          [_ROWNUMBER]
          = MIN(
            SELECT(
              Items[_ROWNUMBER],
              AND(
                ([Building ID] = [_THISROW-1].[Building ID]),
                ([Item] <> “None"),
                ([Location] = [_THISROW-1].[Location])
              )
            )
          )
        )
      )
    ),
    [Location],
      FALSE
  )
>>

[Joseph_Seddik]

Please show screenshots of your tables and an example of the desired results. 

[CaptainEon]

Its a very complicated system. 

But here is an overview of the current issue

Building Table has an Items Table

Each Item will have a Location Field

For each Location in a Building I want to report on all the items in that Location. (Location Loop 1)

Loop 2 shows all the items for that Location 

Then another section in the report for the other locations.

I would have thought 2 START / END loops would have worked by using UNIQUE(). But the UNIUQUE does not work in a START statement.

[Steve]

Putting UNIQUE() around FILTER() has no effect: FILTER() returns row keys, which are already unique.

[SkrOYC]

Adding to @Steve's comments, what do you need exactly? Are you trying to create groups of data? This is not a difficult thing.

Solved: How to separate rows of a table based on unique va... - Google Cloud Community

[Steve]

Try this as the first loop:

<<Start:
  ORDERBY(
    FILTER(
      "Items",
      AND(
        ([Building ID] = [_THISROW].[Building ID]),
        ([Item] <> “None"),
        (
          [_ROWNUMBER]
          = MIN(
            SELECT(
              Items[_ROWNUMBER],
              AND(
                ([Building ID] = [_THISROW-1].[Building ID]),
                ([Item] <> “None")
              )
            )
          )
        )
      )
    ),
    [Location],
      FALSE
  )
>>

I've indented it for clarity, but you must remove the indenting to use it.

[CaptainEon]

Thanks Steve. I'll give it a go.

[SkrOYC]

Afaik, indenting inside any expression shouldn't affect it's usage, neither on templates or the expression assistant

[Joseph_Seddik]

I've been thinking the same, but I haven't tried indentation inside a template.

[SkrOYC]

It works on my side, although I use .html templates instead of GDocs/MSWord.

On those WYSIWYG editors any formula messes with the layout a lot, but I guess after the expressions are replaced with actual values it should look fine

[Steve]

I and others have had problems, but it may only be a problem in certain contexts.

[Joseph_Seddik]

I guess then it would be OK in an html template since spaces are ignored anyway, but would be problematic otherwise. 

[CaptainEon]

Unfortunately this returns he first Location and nothing after.

The following my list of unique locations

UNIQUE(SELECT(ITEMS[Location],
AND
  (
  [Building ID] = [_THISROW].[Building ID],
  [Survey Section] <> "EXCLUDED AREAS"
  )
))

In normal programing I would pick off each locations in the list and process it in a loop.

But the START need a list of IDs

Is there any way to use the list of locations in the above SELECT to filter in the Inner START loop?

[SkrOYC]

Sure but we need to understand your tables schema before we can suggest anything meaningful

[Steve · Accepted solution]

Whoops! I left something out. Try this instead:

<<Start:
  ORDERBY(
    FILTER(
      "Items",
      AND(
        ([Building ID] = [_THISROW].[Building ID]),
        ([Item] <> “None"),
        (
          [_ROWNUMBER]
          = MIN(
            SELECT(
              Items[_ROWNUMBER],
              AND(
                ([Building ID] = [_THISROW-1].[Building ID]),
                ([Item] <> “None"),
                ([Location] = [_THISROW-1].[Location])
              )
            )
          )
        )
      )
    ),
    [Location],
      FALSE
  )
>>

[CaptainEon]

Absolutely brilliant Steve.

You are a 🌟🌟🌟🌟🌟 STAR 🌟🌟🌟🌟🌟

Works a treat.

I understand THISROW-1 in the context of nested START statements. And THISROW in SELECT statements to focus on the current record.

But I cant quite get my head a round the THISROW-1 in the SELECT?

Is it referring to the scope OUTSIDE the SELECT statement?

[Steve · Accepted solution]

Back-references within nested queries ([_THISROW-n])