topic How SQL transform to QlikView? in QlikView

How SQL transform to QlikView?

Fri, 14 May 2010 08:46:04 GMT

I need create following logic in Edit Script (getting data from SQL Server)

Real SQL query is

SELECT *
FROM tblData
WHERE Dat in (SELECT dtPrm2 FROM tblParams)

I tried to do so, but error occured

varS1:
SQL SELECT dPrm1, dtPrm2 FROM tblParams;

varS2:
SQL SELECT *
FROM tblData
WHERE Dat in (LOAD dtPrm2 RESIDENT varS1);

Plz explain me how to resolve this issue in QlikView?

How SQL transform to QlikView?

Fri, 14 May 2010 09:30:55 GMT

Hi,

You can do this with INNER JOIN.

varS1:
SQL SELECT dPrm1, dtPrm2 FROM tblParams;

varS2:
SQL SELECT *
FROM tblData ;

NoConcatenate

varS3:

LOAD dPrm1, dtPrm2 RESIDENT varS1;

INNER JOIN

LOAD *,Dat as dtPrm2 RESIDENT varS2;

Drop Table varS1;

Drop Table varS2;

Re. :Re: How SQL transform to QlikView?

martin59 — Fri, 14 May 2010 09:51:05 GMT

Hello,

I think the "NoConcatenate" instruction must be after the table name. Like this :

varS3 :
NOCONCATENATE
LOAD dPrm1, dtPrm2 RESIDENT varS1;

Regards,

Martin

How SQL transform to QlikView?

Fri, 14 May 2010 11:15:50 GMT

Not work well both script.
The real SQL have to return 1 591 833 records ( if statament " ...WHERE Dat in (SELECT dtPrm2 FROM tblParams)" includes),
but return 1 723 492 (if not includes statament " ...WHERE Dat in (SELECT dtPrm2 FROM tblParams)" )

Re. :Re: How SQL transform to QlikView?

prabhu0505 — Fri, 14 May 2010 11:16:50 GMT

Hi,

I hope you can use preceding load for this. It's simple only, search the forum for appropriate example.

Re. :Re: How SQL transform to QlikView?

prabhu0505 — Fri, 14 May 2010 11:20:43 GMT

Sorry I thought that both tables are same.

How SQL transform to QlikView?

Fri, 14 May 2010 11:22:04 GMT

Hi,

Hope below script will help you.

varS1:

Load *;
SQL SELECT dtPrm2 FROM tblParams;

varS2:

Load * where exists(dtPrm2 ,Dat );
SELECT *
FROM tblData ;

Drop Table varS1;

- Peterson