Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

1 Solution

Accepted Solutions
eddysanchez
Contributor

Re: SQL database: Problems with double rows

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 Replies
MVP
MVP

Re: SQL database: Problems with double rows

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

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

flipside
Valued Contributor II

Re: SQL database: Problems with double rows

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
Contributor

Re: SQL database: Problems with double rows

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;

Not applicable

Re: SQL database: Problems with double rows

Perfect, thanks.

/A

flipside
Valued Contributor II

Re: SQL database: Problems with double rows

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

flipside

Community Browser