Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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...
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
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;
Perfect, thanks.
/A
Whilst this works, self-joining is perfectly acceptable in Qlikview script - at least I haven't hit any issues with it!
flipside