SQL database: Problems with double rows

[#1]

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

[jonathandienst]

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

[#3]

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...

[flipside]

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

[eddysanchez · Accepted solution]

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;

[#6]

Perfect, thanks.

/A

[flipside]

Whilst this works, self-joining is perfectly acceptable in Qlikview script - at least I haven't hit any issues with it!

flipside