Convert sql script into QV script

[ajaykumar1]

Hi All,

I have the following Self join SQL query. This query created on Sales table  I want to know how to write self join in QV scripts.

select a.*, b.partno from Sales a, Sales b

where

a.partno is NULL and b.partno is not NULL

and a.lineno is not NULL and b.lineno is not NULL

and a.invoiceid = 10000 and

a.invoiceid = b.invoiceid and

a.orderno = b.orderno and

a.lineno = b.lineno

please help on this.

Thanks

Ajay

[maxgro]

in this case I would leave everything to the database

load *;

sql

select a.*, b.partno from Sales a, Sales b

where

a.partno is NULL and b.partno is not NULL

and a.lineno is not NULL and b.lineno is not NULL

and a.invoiceid = 10000 and

a.invoiceid = b.invoiceid and

a.orderno = b.orderno and

a.lineno = b.lineno;

is it an option?

[ajaykumar1]

Thanks Massimo.

What it means everything leaves to database?

Is qv supports self join?

More Thanks,

Ajay

[maxgro · Accepted solution]

and this is my try in qlik (if you have a qvd of sales please post it)

a:

load *

where invoiceid=10000;

sql select * from Sales;

inner join (a)

load *;

sql select invoiceid, orderno, lineno,

partno as partnob, lineno as linenob from Sales;

f:

NoConcatenate

load * Resident a

where len(trim(partno))=0 and len(trim(partnob))>0

and len(trim(lineno))>0 and len(trim(linenob))=0;

drop table a;

[maxgro]

the self join is made on database (bold, it's your sql script and the database runs it)

and qlik only load (load *, not bold) the result of self join from the database

load *;

sql

select a.*, b.partno from Sales a, Sales b

where

a.partno is NULL and b.partno is not NULL

and a.lineno is not NULL and b.lineno is not NULL

and a.invoiceid = 10000 and

a.invoiceid = b.invoiceid and

a.orderno = b.orderno and

a.lineno = b.lineno;

[ajaykumar1]

Thanks..

I will try this tmrw my offc timings and will be back with result.

Ajay

[Kushal_Chawda]

You can try like this

a:

Load *

from Sales.qvd(qvd)

where isnull(partno) and

not isnull(lineno) and

invoiceid=10000

inner join

load partno as Partnofrom_b,

invoiceid

orderno

lineno

from Sales.qvd(qvd)

where not isnull(partno) and

not isnull(lineno);

[ajaykumar1]

Thanks Kush,

I will try this approach also and get back.

Ajay.

[ajaykumar1]

Thanks Massimo. You saved my day and its working perfectly.

More Thanks,

Ajay