Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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
Author

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
Specialist II
Specialist II

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
Author

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
Specialist
Specialist

Hi,

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

prabhu0505
Specialist
Specialist

Sorry I thought that both tables are same.

Not applicable
Author

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