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

Hi tamerj1 ,

 

Thank you for ur Solution it's working fine and if we need to add a new data from table 3 to

Concatenate.
tamerj1's avatar
tamerj1
Community Champion
4 years ago

arvindarvind24 
Sure Please provide more details

  • 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]
        )