Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Inner / embedded select in select of data

Is it possible within Qlikview to work with embedded 'Select' within a 'Select' statement  

 

Reason :

- TABLE1 : on one hand I have a table as a result from another procedure  ( cfr. specific accounts that fit a certain profile eg.  turnover and profit and loss ) . We do not know in advance which accounts will popup 😉 

- TABLE2 : secondly I have a huge large table with data , but only want to see the detailed and long historical data from content that is also in the first table  ( cfr. only for these accounts ) 

Problem : both tables are on different environments an I can not join them ( pfff..)  

 

Is it possible to do either

FIRST APPROACH  ( unlikely ….) 

 - First upload TABLE1 

- In the import statement of TABLE2 select with the resident information of TABLE1? so to only import what I need to have (only the accounts I wanna research)

=> Am I correct to assume this will fail as the 2nd import can not blend resident data with external data ?

 

2ND APPROACH : 

Do I first need to import both TABLES  and then approach the imported data in TABLE2 as a resident QVD and create a new table that only retains the lines in TABLE2 on the basis of data in TABLE1 , so with an embedded select in a select 

 

TABLE2_new : 

select *  from TABLE2 where account in (select distinct account from TABLE1) 

 

Or are there better ways to do this even more efficiently ? 

 

 

4 Replies
Highlighted
MVP & Luminary
MVP & Luminary

If there aren't too many accounts in table 1 you could concat them and then applying them within a in() where-clause to the second load. Most databases/driver have a limitation to the number of the in() parameter - I saw often the number of 1000 as limit.

If there are more values you would need to bring these data into the second database (it may just a simple and temporary csv-import) to use them within an inner join as a filter.

An alternatively could be to load the whole second table in Qlik and saving it as qvd (by huge data it might be done incrementally) and then just using an exists() where-clause to filter your final table.

- Marcus

Highlighted
MVP & Luminary
MVP & Luminary

I don't think there is any reason to create a QvD to use where exists(). You could just code it like this:

Table1:
SQL SELECT * FROM TAB1;

Table2:
LOAD *
Where Exists(AccountID)
;
SQL SELECT * FROM TAB2;

-Rob

Highlighted
Contributor II
Contributor II

Marcus ,

 

I was actually thinking to use an inner join indeed but din't think if I would first read in the values (of the accounts) on 1 location (so it becomes resident)

and then do the 2nd inquiry (SQL script on the mainframe ), that I could use the resident table inside the import of the mainframe SQL ...

As it comes the first query probably will return about 10 accounts and retrieve data from these accounts only for import . That would then be a few hundred lines. The actually table over which the 2nd query rund holds million of lines (which I do not all want to import obviously).

So would it be like this

TAB1:

Select account id from table1;

 

Select * from table2

where accountid in ( select distinct accountid from resident tab1 ) ;

or do you mean something else ?

Will try the above out and keep you update...

Thanks laready for thinking along 😉

 

 

 

 

Highlighted
MVP & Luminary
MVP & Luminary

Within the SQL sub-query you couldn't access a Qlik resident-table else only data/tables which are available within your database (Qlik didn't execute the SQL else just transfers the statement to the database - and within the database the resident-table is unknown and not available). But if it are just a few hundreds accounts a where-clause with in() should work. For it you need to concatenate the values within a string, for example with something like this:

TAB1:  Select account id from table1;

GetAccount: load concat([account id], ', ') as string resident TAB1;
let vAccountID = peek('string', 0, 'GetAccount');

TAB2: Select * from table2

where accountid in($(vAccountID)) ;

- Marcus