Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

How SQL transform to QlikView?

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?

1 Solution

Accepted Solutions
Not applicable

How SQL transform to QlikView?

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

View solution in original post

6 Replies
Not applicable

How SQL transform to QlikView?

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;

martin59
Valued Contributor II

Re. :Re: How SQL transform to QlikView?

Hello,

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

varS3 :
NOCONCATENATE
LOAD dPrm1, dtPrm2 RESIDENT varS1;


Regards,

Martin

Not applicable

How SQL transform to QlikView?

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

prabhu0505
Valued Contributor

Re. :Re: How SQL transform to QlikView?

Hi,

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

prabhu0505
Valued Contributor

Re. :Re: How SQL transform to QlikView?

Sorry I thought that both tables are same.

Not applicable

How SQL transform to QlikView?

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

View solution in original post