topic Re: Convert sql script into QV script in QlikView

Convert sql script into QV script

ajaykumar1 — Tue, 30 Jun 2015 18:04:45 GMT

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

Re: Convert sql script into QV script

maxgro — Tue, 30 Jun 2015 18:16:49 GMT

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?

Re: Convert sql script into QV script

ajaykumar1 — Tue, 30 Jun 2015 18:19:33 GMT

Thanks Massimo.

What it means everything leaves to database?

Is qv supports self join?

More Thanks,

Ajay

Re: Convert sql script into QV script

maxgro — Tue, 30 Jun 2015 18:20:13 GMT

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

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;

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;

Re: Convert sql script into QV script

maxgro — Tue, 30 Jun 2015 18:22:41 GMT

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;

Re: Convert sql script into QV script

ajaykumar1 — Tue, 30 Jun 2015 18:26:12 GMT

Thanks..

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

Ajay

Re: Convert sql script into QV script

Kushal_Chawda — Tue, 30 Jun 2015 18:30:41 GMT

You can try like this

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);

Re: Convert sql script into QV script

ajaykumar1 — Tue, 30 Jun 2015 18:33:46 GMT

Thanks Kush,

I will try this approach also and get back.

Ajay.

Re: Convert sql script into QV script

ajaykumar1 — Wed, 01 Jul 2015 13:10:38 GMT

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

More Thanks,

Ajay