Inner / embedded select in select of data

[HWallays]

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 ? 

[marcus_sommer]

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

[rwunderlich]

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

[HWallays]

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 😉

[marcus_sommer]

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

[HWallays]

Have been able to solve the above with buiding the string (only a few lines so it worked .. thanks).  Currently I have to perform it on a much larger scale (with alot more data) and then the string solutions will not work I fear. 

Below what I try to achieve to only retrieve alle information in QVD1 (mutliple lines per contract) where the contract exists in QVD2 (also with multiple lines).

LOAD * FROM
[C:\FINANCE_DATA.QVD] (qvd) where CONTRACT_REFERENCE in

(LOAD distinct AGREEMENT_ID FROM
[C:\BUSINESS_DATA2.Qvd] (qvd)); 

How would you formulate subquery in the green part ?

PS. I can not join the tables as there is a n:m relation

[marcus_sommer]

You could try it in this way:

LOAD distinct AGREEMENT_ID as CONTRACT_REFERENCE FROM [C:\BUSINESS_DATA2.Qvd] (qvd); 

LOAD * FROM [C:\FINANCE_DATA.QVD] (qvd) where exists(CONTRACT_REFERENCE);

- Marcus