topic Re: Where Sql in QlikView

Where Sql

andrea90casa — Thu, 07 Sep 2017 12:45:11 GMT

Hi at all,

I have this issue:

I load a table called TOrdine in this way:

TOrdine:

SQL Select

numero as Progetto_Numero,

CONFERMATO

From dbo.TORDINE

WHERE CONFERMATO = 1

;

I need to use where function because i filter only data in "numero" field with field "CONFERMATO" = 1.

Then i have a second table called Ordini:

Ordini:

SQL Select

CODICE as COD_ORD,

CODMAGG,

VAROPZ as VAROPZ_01,

NUMERO as Progetto_Numero,

CODMAGG + VAROPZ as COD_MAGG,

RIGA,

id,

Tipo,

pr,

NOTE as QUANTITA,

VARIANTI

From dbo.PORDINE

;

I would like to create a unique table called Ordini with only records with CONFERMATO = 1 (so only the article of the first table).

I tried to use a preceding load like this one, but i gave to me errors:

TOrdine:

SQL Select

numero as Progetto_Numero,

CONFERMATO

From dbo.TORDINE

WHERE CONFERMATO = 1

;

Ordini:

Load

CODICE as COD_ORD,

CODMAGG,

VAROPZ as VAROPZ_01,

NUMERO as Progetto_Numero,

CODMAGG + VAROPZ as COD_MAGG,

RIGA,

id,

Tipo,

pr,

NOTE as QUANTITA,

VARIANTI

WHERE EXISTS (Progetto_Numero);

SQL Select

CODICE as COD_ORD,

CODMAGG,

VAROPZ as VAROPZ_01,

NUMERO as Progetto_Numero,

CODMAGG + VAROPZ as COD_MAGG,

RIGA,

id,

Tipo,

pr,

NOTE as QUANTITA,

VARIANTI

From dbo.PORDINE

;

DROP TABLE TOrdine;

----- Is there any way in order to get the expected result?----

Thanks in advance

Andrea

Re: Where Sql

Anil_Babu_Samineni — Thu, 07 Sep 2017 12:55:20 GMT

Exist function is for Qlikview not SQL. You could check simple Preceding Load to achieve that? OR Resident

Re: Where Sql

its_anandrjs — Thu, 07 Sep 2017 13:11:52 GMT

Try this could worked for you

TOrdine:

Load

numero as Progetto_Numero,

CONFERMATO

WHERE CONFERMATO = 1;

SQL Select

numero,

CONFERMATO

From dbo.TORDINE

WHERE CONFERMATO = 1;

Ordini:

Load

CODICE as COD_ORD,

CODMAGG,

VAROPZ as VAROPZ_01,

NUMERO as Progetto_Numero,

CODMAGG + VAROPZ as COD_MAGG,

RIGA,

id,

Tipo,

pr,

NOTE as QUANTITA,

VARIANTI;

SQL Select

CODICE ,

CODMAGG,

VAROPZ,

NUMERO ,

CODMAGG + VAROPZ,

RIGA,

id,

Tipo,

pr,

NOTE ,

VARIANTI

From dbo.PORDINE;

Re: Where Sql

andrea90casa — Thu, 07 Sep 2017 13:20:15 GMT

Hi Anand,

But in this script there isn't the where exist function so I can't filter the records in the second table according to the records of first table (with value of CONFERMATO = 1).

Or i missed something?

Andrea

Re: Where Sql

its_anandrjs — Thu, 07 Sep 2017 13:30:02 GMT

Then try this way or you can create the QVD of this tables or resident load any one of this

TOrdine:

Load

numero as Progetto_Numero,

CONFERMATO

WHERE CONFERMATO = 1;

SQL Select

numero,

CONFERMATO

From dbo.TORDINE

WHERE CONFERMATO = 1;

Ordini:

Load

CODICE as COD_ORD,

CODMAGG,

VAROPZ as VAROPZ_01,

NUMERO,

CODMAGG + VAROPZ as COD_MAGG,

RIGA,

id,

Tipo,

pr,

NOTE as QUANTITA,

VARIANTI;

SQL Select

CODICE ,

CODMAGG,

VAROPZ,

NUMERO ,

CODMAGG + VAROPZ,

RIGA,

id,

Tipo,

pr,

NOTE ,

VARIANTI

From dbo.PORDINE;

//////////////////////////////////

Noconcatenate

Load

Progetto_Numero,CONFERMATO

Resident TOrdine;

Drop Table TOrdine;

FinalTable:
Load

Resident Ordini Where Exists(Progetto_Numero,NUMERO);

Drop Table Ordini;

Re: Where Sql

andrea90casa — Thu, 07 Sep 2017 14:32:38 GMT

Hi Anand,

It is not working with the last script so i tried to change in qvd file. For example first table:

TOrdine:

SQL Select

numero,

CONFERMATO

From dbo.TORDINE

WHERE CONFERMATO = 1

;

Store TOrdine into TOrdine.qvd(qvd);

Drop Table TOrdine;

TOrdine:

LOAD

numero as Progetto_Numero,

CONFERMATO

From TOrdine.qvd;

But it doesn't find the field numero, do you know why?

Re: Where Sql

andrea90casa — Thu, 07 Sep 2017 14:37:31 GMT

Sorry Anand,

I find now the issue.

So i can create the second qvd with the second table.

But then how can i filter by CONFERMATO?

Re: Where Sql

Peter_Cammaert — Thu, 07 Sep 2017 14:37:56 GMT

Probably because a RDBMS may decide to return all field names in upper case (ANSI standard), even if you specify their names in lower case in the original SQL SELECT.

Replace the current LOAD ... FROM QVD with a LOAD * FROM QVD and check the field names in the QlikView Table Viewer.

Re: Where Sql

Kushal_Chawda — Thu, 07 Sep 2017 14:39:20 GMT

TOrdine:

LOAD *;

SQL Select

numero as Progetto_Numero,

CONFERMATO

From dbo.TORDINE

WHERE CONFERMATO = 1

Ordini:

LOAD *

where exists(Progetto_Numero);

SQL Select

CODICE as COD_ORD,

CODMAGG,

VAROPZ as VAROPZ_01,

NUMERO as Progetto_Numero,

CODMAGG + VAROPZ as COD_MAGG,

RIGA,

id,

Tipo,

pr,

NOTE as QUANTITA,

VARIANTI

From dbo.PORDINE;

drop table TOrdine;

TOrdine:

LOAD *;

SQL Select

numero as Progetto_Numero,

CONFERMATO

From dbo.TORDINE

WHERE CONFERMATO = 1

Ordini1:

LOAD *;

SQL Select

CODICE as COD_ORD,

CODMAGG,

VAROPZ as VAROPZ_01,

NUMERO as Progetto_Numero1

CODMAGG + VAROPZ as COD_MAGG,

RIGA,

id,

Tipo,

pr,

NOTE as QUANTITA,

VARIANTI

From dbo.PORDINE;

Ordini:

noconcatenate

LOAD *

resident Ordini1

where exists(Progetto_Numero,Progetto_Numero1);

drop tables Ordini1,TOrdine;

Re: Where Sql

andrea90casa — Thu, 07 Sep 2017 14:47:20 GMT

Hi tried before a solution equal to the first one but it gaves to me this error:

Field not found - <Progetto_Numero>

SQL Select

CODICE,

CODMAGG,

VAROPZ,

NUMERO,

RIGA,

id,

Tipo,

pr,

NOTE,

VARIANTI

From dbo.PORDINE

Re: Where Sql

Kushal_Chawda — Thu, 07 Sep 2017 15:01:42 GMT

try another one which I have suggested

Re: Where Sql

andrea90casa — Thu, 07 Sep 2017 15:09:18 GMT

Even the other one gave to me an error.

I tried to create qvd file with this script:

TOrdine:

SQL Select

numero,

CONFERMATO

From dbo.TORDINE

WHERE CONFERMATO = 1

;

Store TOrdine into TOrdine.qvd(qvd);

Drop Table TOrdine;

TOrdine:

LOAD

numero as Progetto_Numero,

CONFERMATO

From CxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxTOrdine.qvd(qvd);

Ordine:

SQL Select

CODICE,

CODMAGG,

VAROPZ,

NUMERO,

RIGA,

id,

Tipo,

pr,

NOTE,

VARIANTI

From dbo.PORDINE

;

Store Ordine into Ordine.qvd(qvd);

Drop Table Ordine;

Ordine:

LOAD

CODICE as COD_ORD,

CODMAGG,

VAROPZ as VAROPZ_01,

NUMERO as Progetto_Numero,

CODMAGG + VAROPZ as COD_MAGG,

RIGA,

id,

Tipo,

pr,

NOTE as QUANTITA,

VARIANTI

From xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\Ordine.qvd(qvd)

;

And everything it's correct, but when I insert the

Where Exists function it gave to me the error because it doesn't find the field Progetto_Numero and i don't know why.

Re: Where Sql

edmondo_tassi — Thu, 07 Sep 2017 15:09:30 GMT

Why don't use left join or left keep?

Re: Where Sql

Peter_Cammaert — Thu, 07 Sep 2017 15:24:54 GMT

Try again but replace your current WHERE Exists condition with something like:

WHERE Exists(Progetto_Numero, NUMERO)

when loading the last table from QVD. To avoid getting an AUTOCONCATENATE in the last LOAD, you can add a NOCONCATENATE prefix to the LOAD as well.

Re: Where Sql

Peter_Cammaert — Thu, 07 Sep 2017 15:26:10 GMT

Indeed, LEFT KEEP would be the better choice in this situation.

Re: Where Sql

andrea90casa — Thu, 07 Sep 2017 15:42:53 GMT

Thanks to all,

The problem was the where sintax.

I don't know why but maybe he read the old name and so i wanted NUMERO and not Progetto_Numero as second parameter