3 Replies Latest reply: May 25, 2016 6:47 AM by Stefan Wühl RSS

    How can I remove dupes from a table?

    annick whitfield

      Hello,

       

      Here's another issue I am facing. Thanks in advance for your help:

       

      I load this table:

       

      LEISURE:

      Load * inline

      [LOCATION,  TYPE,        GROUP,

      PARIS,          CITY,          CULTURE

      PARIS,          CITY,          MUSIC                     /* [PARIS, CITY] is a DUPE, I would like to discard it

      ENGLAND,   COUNTRY, MUSIC

      ];

       

      I obtain:

       

      How can I transform that table into a smaller table, discarding the 2nd occurrence with PARIS, CITY with GROUP MUSIC and only keeping the 1st occurrence PARIS, CITY with GROUP=CULTURE.

       

      I would like to have this LEISURE table at the end:

       

       

      Many thanks. Annick

        • Re: How can I remove dupes from a table?
          Stefan Wühl

          Maybe like

           

          LEISURE:

          Load *, LOCATION & TYPE as DupeKey inline

          [LOCATION,  TYPE,        GROUP,

          PARIS,          CITY,          CULTURE

          PARIS,          CITY,          MUSIC                     /* [PARIS, CITY] is a DUPE, I would like to discard it

          ENGLAND,   COUNTRY, MUSIC

          ] WHERE NO EXISTS(DupeKey, LOCATION & TYPE);

           

          DROP FIELD DupeKey;

          • Re: How can I remove dupes from a table?
            Sunny Talwar

            May be like this:

             

            LEISURE:

            LOAD LOCATION,

                      TYPE,

                      MinString(GROUP) as GROUP

            Group By LOCATION, TYPE;

            Load * inline

            [LOCATION,  TYPE,        GROUP,

            PARIS,          CITY,          CULTURE

            PARIS,          CITY,          MUSIC                     /* [PARIS, CITY] is a DUPE, I would like to discard it

            ENGLAND,   COUNTRY, MUSIC

            ];

              • Re: How can I remove dupes from a table?
                Stefan Wühl

                If the OP is interested in the first occurence, I would then rather use

                 

                LEISURE:

                LOAD LOCATION,

                          TYPE,

                          FirstValue(GROUP) as GROUP

                Group By LOCATION, TYPE;

                Load * inline

                [LOCATION,  TYPE,        GROUP,

                PARIS,          CITY,          CULTURE

                PARIS,          CITY,          MUSIC                    /* [PARIS, CITY] is a DUPE, I would like to discard it

                ENGLAND,  COUNTRY, MUSIC

                ];