Accessing Data from Table Loaded Earlier in Script

[skustes]

I am trying to load several cascading levels of content. To put it simply, a client has 1 or more accounts, an account contains 1 or more pieces of content. I would like to use the information pulled into the first table to limit the information that I pass back to SQL to get the next level of data .

The part that I am unable to figure out is how to access the data that has already been loaded earlier in the script. The last line in the code block is where I'm faltering. Is there a way to do this?

Here's a simplified version of what I want to achieve:

LOAD account_uid,
account_name;

[accounts]:
SELECT account_uid, account_name
FROM db.accounts
WHERE client_uid IN ('uuid_1', 'uuid_2', 'uuid_3');

LOAD account_uid,
content_uid,
content_name;

[content]:
SELECT account_uid, content_uid, content_name
FROM db.content
WHERE account_uid IN (<The account UIDs loaded in the previous table>);

I could use the first query in place of the bolded line as a subquery, but that seems inefficient since I've already retrieved the account UIDs that I need to send back and I will be using the same account UIDs multiple times.

[Oleg_Troyansky · Accepted solution]

Hi there,

There are a couple of ways to accomplish this. First, we should keep in mind that there is no elegant way of passing data from Qlik back to the source database. With that in mind, here are your options:

1. Use LEFT KEEP(accounts) in front of the subsequent LOAD. This way, only matching accounts will get loaded into the second table. However, the SQL SELECT command will still fetch all the data, so this method is simple, but wasteful. It's acceptable if the volume of data is not too big.

2. Formulate a more complex SQL SELECT statement that would limit the data at the database level. Something along these lines:

SELECT account_uid, content_uid, content_name
FROM db.content
WHERE account_uid IN (SELECT account_uid
FROM db.accounts
WHERE client_uid IN ('uuid_1', 'uuid_2', 'uuid_3')
);

So, essentially you need to repeat a large part of the first SQL SELECT as a nested SELECT, in order to limit the accounts to those that were loaded before.
This method is a bit more involved, but it is less wasteful that the first one.

Cheers,

[skustes]

Understood. So subqueries it is. Thanks!