topic Re: Can someone please help? in QlikView

Can someone please help?

samvile18 — Thu, 29 May 2014 15:33:37 GMT

I have the following script which joins a couple of tables by UniqueKey, what I now want to do is create an additional field based on the newly created table. I can get it to work but the data is growing when I don't believe is should be, can anyone suggest what I'm doing wrong...or maybe a better way of doing it?

AWDCombined:

LOAD UniqueKey,

SchemeNo

FROM [Combined.qvd]

Left Join (AWDCombined)

LOAD UniqueKey,

NGSSchemeNo;

SQL SELECT *

FROM FPWFI;

Left Join (AWDCombined)

LOAD UniqueKey,

if(Len(SchemeNo)>6,NGSSchemeNo,SchemeNo) as RealSchemeNo

Resident AWDCombined;

Like I say, the above does work, it just seems to cause the underlying data to grow and I don't know why.

Re: Can someone please help?

manideep78 — Thu, 29 May 2014 15:58:35 GMT

Tryt this,

AWDCombined:

LOAD *, if(Len(SchemeNo)>6,NGSSchemeNo,SchemeNo) as RealSchemeNo;

LOAD UniqueKey,

SchemeNo

FROM [Combined.qvd]

Left Join (AWDCombined)

LOAD UniqueKey,

NGSSchemeNo;

SQL SELECT *

FROM FPWFI;

Re: Can someone please help?

maxgro — Thu, 29 May 2014 19:57:37 GMT

the data grow? is the UniqueKey really unique (in qvd and in db)?

Re: Can someone please help?

preminqlik — Thu, 29 May 2014 20:19:26 GMT

try this : use distinct

AWDCombined:

LOAD distinct UniqueKey,

SchemeNo

FROM [Combined.qvd]

Left Join (AWDCombined)

LOAD distinct UniqueKey,

NGSSchemeNo;

SQL SELECT *

FROM FPWFI;

Left Join (AWDCombined)

LOAD distinct UniqueKey,

if(Len(SchemeNo)>6,NGSSchemeNo,SchemeNo) as RealSchemeNo

Resident AWDCombined;