topic Re: SQL database: Problems with double rows in QlikView

SQL database: Problems with double rows

Thu, 07 Mar 2013 12:16:17 GMT

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.

Re: SQL database: Problems with double rows

jonathandienst — Thu, 07 Mar 2013 12:38:05 GMT

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

Thu, 07 Mar 2013 12:45:11 GMT

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

flipside — Thu, 07 Mar 2013 13:21:02 GMT

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

eddysanchez — Thu, 07 Mar 2013 13:39:19 GMT

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;

Re: SQL database: Problems with double rows

Thu, 07 Mar 2013 13:42:10 GMT

Perfect, thanks.

Re: SQL database: Problems with double rows

flipside — Thu, 07 Mar 2013 13:46:27 GMT

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

flipside