Forum Discussion

RvdHeijden's avatar
RvdHeijden
Post Prodigy
8 years ago
Solved

Summarizing one table in a new table

Goodday,   How can i summarize 1 table into a new table if i want the following result   Mainproject            City A                             A A                             A A          ...
Need Help
RvdHeijden's avatar
RvdHeijden
Post Prodigy
8 years ago

Zubair_Muhammad

Its hard to explain but im gonna try none the less.

 

I now have tabled based on 'City' names and a few calculated colums such as 'total amount of adresses', 'adresses that have status x', 'adresses that have status y' etc etc.

 

But that is based on the city and now im missing the column for the 'Projectname'.


For example there are 1483 homes in the city of Avenhorn but it's a possibility that 1000 homes are in Project A en 483 are in Project B. I need to be able to make that difference so i need to add that column.

So basically if Avenhorn is in 2 different projects i need to rows for Avenhorn telling me how many homes are in Project A en how many are in Project B.

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    RvdHeijden

     

    Hi,

     

    Please could you provide some data (that can be copied) and expected results?

    • RvdHeijden's avatar
      RvdHeijden
      Post Prodigy
      8 years ago

      I thought i did that in my last reply Zubair_Muhammad ðŸ˜‰
      I gave you more information and a screenshot of all the cities but im missing the projectnames, they are in a different table.
      Is it possible to create a new table with a colum with the projectname and 1 column with the unique cities for each projectname

       

      I can't attach an excel file but it's not that difficult what im asking but let me try again.

       

      I have one table with 40.000 rows in them.

      Every row has a City and a ProjectName in them

      Both City and Projectnames are NOT unique

      A City is always in a Project and a project is always in a city

       

      What i need is the following

       

      A new table to summarize that is Project A has 12000 rows and has 10 unique rows

       

      A summarization of the unique City names per Projectname

      So if Projectname A has 12.000 rows in the table but has 4 unique Citynames then the result should be

       

      Project A                 City A

      Project A                 City B

      Project A                 City C

      Project A                 City D

       

       

      If the next Projectnam has 8000 rows and 2 unique cities, the result should be

       

      Project B                 City D

      Project B                 City E

       

      As you can see City D is both in Project A as in Project B and that is what i need

      I need a complete summarization (in a new table) of all 40.000 rows

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        RvdHeijden

         

        Please send me your Excel File.

         

        I think Distinct(TableName) should do the job. Because it looks at distinct row...not a particular column