Forum Discussion

arvindarvind24's avatar
arvindarvind24
Helper II
4 years ago
Solved

Concatenate two data from different tables without reference

Concatenate two data from different tables without reference Sample data and output Style No.(Table1) Region(Table2) Concatenate(Output) ABCD03700I BIHAR ABCD03700I,BIHAR ABCD06299 BL...
  • amitchandak's avatar
    amitchandak
    4 years ago

    arvindarvind24 , crossjoin

     

    new table = Crossjoin(Table1, Table2)

     

    or

     

    new table = addcolumns(Crossjoin(Table1, Table2) , "Concat", [Style No] & ", " & [Region]) 

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi arvindarvind24 
    Here is a sample file with the solution https://we.tl/t-IHBj2FTgwN

     

    Concatenated Table = 
SELECTCOLUMNS (
    GENERATE (
        Table1,
        ADDCOLUMNS ( 
            Table2,
            "@Concatenate", Table1[Style No.] & "," & Table2[Region]
        )
    ),
    "Concatenate", [@Concatenate]
)

     

     

arvindarvind24's avatar
arvindarvind24
Helper II
4 years ago

Please find the details below.

 

 

Style No.(Table1)Rank1(Table3-Measurestbl)Region(Table2)Concatenate(Output)
ABCD03700I1BIHARABCD03700I,1,BIHAR
ABCD062990BLR, MYS & TMKRABCD03700I,1,BLR, MYS & TMKR
ABCD084612UTTAR PRADESHABCD03700I,1,UTTAR PRADESH
ABCD08684B4TAMIL NADUABCD03700I,1,TAMIL NADU
ABCD08684E0TELANGANAABCD03700I,1,TELANGANA
ABCD0929715ORISSAABCD03700I,1,ORISSA
ABCD0930210MH,GOA & GJABCD03700I,1,MH,GOA & GJ
ABCD093511DL & PBABCD03700I,1,DL & PB
ABCD097781 ABCD06299,0,BIHAR
ABCD09838B0 ABCD06299,0,BLR, MYS & TMKR
   ABCD06299,0,UTTAR PRADESH
   ABCD06299,0,TAMIL NADU
   ABCD06299,0,TELANGANA
   ABCD06299,0,ORISSA
   ABCD06299,0,MH,GOA & GJ
   ABCD06299,0,DL & PB
   ABCD08461,2,BIHAR
   ABCD08461,2,BLR, MYS & TMKR
   ABCD08461,2,UTTAR PRADESH
   ABCD08461,2,TAMIL NADU
   ABCD08461,2,TELANGANA
   ABCD08461,2,ORISSA
   ABCD08461,2,MH,GOA & GJ
   ABCD08461,2,DL & PB
   ABCD08684B,4,BIHAR
   ABCD08684B,4,BLR, MYS & TMKR
   ABCD08684B,4,UTTAR PRADESH
   ABCD08684B,4,TAMIL NADU
   ABCD08684B,4,TELANGANA
   ABCD08684B,4,ORISSA
   ABCD08684B,4,MH,GOA & GJ
   ABCD08684B,4,DL & PB
   ABCD08684E,0,BIHAR
   ABCD08684E,0,BLR, MYS & TMKR
   ABCD08684E,0,UTTAR PRADESH
   ABCD08684E,0,TAMIL NADU
   ABCD08684E,0,TELANGANA
   ABCD08684E,0,ORISSA
   ABCD08684E,0,MH,GOA & GJ
   ABCD08684E,0,DL & PB
   ABCD09297,15,BIHAR
   ABCD09297,15,BLR, MYS & TMKR
   ABCD09297,15,UTTAR PRADESH
   ABCD09297,15,TAMIL NADU
   ABCD09297,15,TELANGANA
   ABCD09297,15,ORISSA
   ABCD09297,15,MH,GOA & GJ
   ABCD09297,15,DL & PB
  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    arvindarvind24 
    The sample expected result does not clarify whether this is a crossjoin of the three tables or a merge between table1 and table3 then a crossjoj with table2. The complete crossjoin would be

    Concatenated Table = 
SELECTCOLUMNS (
    GENERATE (
        Table1,
        GENERATE (
            Table2,
            ADDCOLUMNS ( 
                Table3,
                "@Concatenate", Table1[Style No.] & "," & Table3[Rank1] & "," & Table2[Region]
            )
        )
    ),
    "Concatenate", [@Concatenate]
)
    • arvindarvind24's avatar
      arvindarvind24
      Helper II
      4 years ago

      tamerj1 

       

      If I want to use a cross join this will work?

       

      oncatenatedTable =
      SELECTCOLUMNS (
      GENERATE (
      ITEMMASTER,
      GENERATE (
      Measurestbl,
      ADDCOLUMNS (Crossjoin(BaseStock),
      "@Concatenate",
      ITEMMASTER[STYLE_NUMBER] & "," & BaseStock[Region] & "," & Measurestbl[RANK11]
      )
      )
      ),
      "Concatenate", [@Concatenate]
      )

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Hi arvindarvind24 
        Please use

        Concatenated Table =
SELECTCOLUMNS (
    GENERATE (
        ITEMMASTER,
        GENERATE (
            BaseStock,
            ADDCOLUMNS (
                Measurestbl,
                "@Concatenate",
                    ITEMMASTER[Style No.] & "," & Measurestbl[Rank1] & "," & BaseStock[Region]
            )
        )
    ),
    "Concatenate", [@Concatenate]
)