How SQL transform to QlikView?

[#1]

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?

[#2]

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]

Hello,

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

varS3 :
NOCONCATENATE
LOAD dPrm1, dtPrm2 RESIDENT varS1;

Regards,

Martin

[#4]

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]

Hi,

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

[prabhu0505]

Sorry I thought that both tables are same.

[#7 · Accepted solution]

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