6 Replies Latest reply: Mar 7, 2013 8:46 AM by Dave Riley RSS

    SQL database: Problems with double rows

      Hi

      I added a "Join" to my script and then I got double rows, half of them got the columns I have added with the "Join" and half of them doesn't.

      I've tried to ad "Distinct" to "Load" and "SQL Select", but it doesn't help. I've also tried concat instead of join, but it doesn't help...

      The script is attached to this post.

      Thanks in advance.

      /A

        • Re: SQL database: Problems with double rows
          Jonathan Dienst

          Hi

           

          Hard to say as you are using Load *, but I see that you have dropped ID before the join. This could then result in duplicate rows which were distinct at load only because of the ID field. These duplicates could cause problems. Try the script without dropping the ID field.

           

          Regards

          Jonathan

            • Re: SQL database: Problems with double rows

              I tried to remove "Drop Field", but it didn't change. I also removed all the distinct after this but it didn't help. Then I added "Drop Field" after the join-expression, tried that, and then added Distinct again but it didn't help...

                • Re: SQL database: Problems with double rows
                  Dave Riley

                  Hi Anna,

                   

                  Your problem will be linked to the null values in your data creating duplicates. If every one of your records has at least 1x null field (which it may do for lots of columns), it will appear to double your row count.

                   

                  Try joining on one unique row identifier instead of *, or create your own similar to this ...

                   

                  data:

                  load  rowno() as rowid, *, 'data1' as source;

                  SQL SELECT distinct * FROM ...;

                  concatenate load  rowno() as rowid, *, 'data2' as source;

                  SQL SELECT distinct * FROM ...;

                  {etc}

                   

                  join (data)

                  load  rowid, if( ... ) as ... resident data;

                   

                  Hope this helps

                   

                   

                  flipside

                  • Re: SQL database: Problems with double rows
                    Eddy Sanchez

                    Anna, you use join for the same table, It doesn't seem a good practice

                     

                    it's better to use something like:

                     

                    FinalTable:

                    Load Distinct <describe all your fields>,

                              if (Ålder=1, '-15år',

                              if (Ålder=2, '16-24år',

                              if (Ålder=3, '25-34år',

                              if (Ålder=4, '35-44år',

                              if (Ålder=5, '45-54år',

                              if (Ålder=6, '55-64år',

                              if (Ålder=7, '65-år'))))))) as ÅlderKlartext,

                     

                              if (Kön=1, 'Kille/Man',

                              if (Kön=2, 'Tjej/Kvinna',

                              if (Kön=3, 'Vill ej ange'))) as KönKlartext,

                             

                              if (Besökningsfrekvens=1, '1-3 ggr/veckan',

                              if (Besökningsfrekvens=2, '4-7 ggr/veckan',

                              if (Besökningsfrekvens=3, '1-3 ggr/mån',

                              if (Besökningsfrekvens=4, 'Annat')))) as BesökKlartext,

                     

                              if (Informationsväg=1, 'Annons',

                              if (Informationsväg=2, 'Annonsbladet',

                              if (Informationsväg=3, 'Masen',

                              if (Informationsväg=4, 'Internet',

                              if (Informationsväg=5, 'Telefon',

                              if (Informationsväg=6, 'Vänner/fam.',

                              if (Informationsväg=7, 'Annat'))))))) as InfoKlartext

                    Resident Fritid;

                     

                    drop table Fritid;