Forum Discussion

krishna_murthy's avatar
krishna_murthy
Helper II
1 year ago
Solved

Inner Join count rows

Hi Team,   Please find the below table and do the Table1: ID, Name 1, A 1, B  table 2: ID, Location 1,D 1,E 2,F   What is the Inner join count rows? what is the left outer join count ro...
Need a help
  • ashleyfiore's avatar
    ashleyfiore
    1 year ago
    Inner Join Row Count :=
COUNTROWS (
    GENERATE(
        Table1,
        FILTER(
            Table2,
            Table2[ID] = Table1[ID]
        )
    )
)

     

    Left Outer Join Row Count :=
COUNTROWS (
    GENERATE (
        Table1,
        VAR MatchingRows =
            FILTER (
                Table2,
                Table2[ID] = Table1[ID]
            )
        RETURN
            IF (
                COUNTROWS(MatchingRows) > 0,
                MatchingRows,
                ROW ( "ID", BLANK(), "Location", BLANK() )  -- simulate left join with blanks
            )
    )
)
ashleyfiore's avatar
ashleyfiore
Advocate IV
1 year ago
Inner Join Row Count :=
COUNTROWS (
    GENERATE(
        Table1,
        FILTER(
            Table2,
            Table2[ID] = Table1[ID]
        )
    )
)

 

Left Outer Join Row Count :=
COUNTROWS (
    GENERATE (
        Table1,
        VAR MatchingRows =
            FILTER (
                Table2,
                Table2[ID] = Table1[ID]
            )
        RETURN
            IF (
                COUNTROWS(MatchingRows) > 0,
                MatchingRows,
                ROW ( "ID", BLANK(), "Location", BLANK() )  -- simulate left join with blanks
            )
    )
)