Is it possible within Qlikview to work with embedded 'Select' within a 'Select' statement
- 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
select * from TABLE2 where account in (select distinct account from TABLE1)
Or are there better ways to do this even more efficiently ?
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.
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
Select account id from table1;
Select * from table2
where accountid in ( select distinct accountid from resident tab1 ) ;
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');