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 to select data upon a dimension not available in the dataset?

Hi All,

there's a configurational database that stores all database names that I need to select. All these databases have identical structure. Within these databases there's a table (Settings) with a dim (Dimension) upon which I need to decide whether I will select or not table Data within these databases. The table Settings doesn't have any dimension to join it with table Data. Please see the below code and suggest what should I do to select only the dbs where Dimension  = 1.

Table_1:

SELECT DB_Name FROM DB_Conf;

Table_2:

LET A = NoOfRows('Table_1');

FOR i=0 TO $(A)-1

LET B = peek('DB_Name',$(i),'DZ_Bazy');

LET C = peek('DB_Name',$(i),'DZ_Bazy')&'.CDN.Settings';

SELECT

'$(B)' AS DB_Name,

Dimension

FROM $(C);

NEXT

Table_3:

LET A = NoOfRows('Table_1');

FOR i=0 TO $(A)-1

LET B = peek('DB_Name',$(i),'DZ_Bazy');

LET C = peek('DB_Name',$(i),'DZ_Bazy')&'.CDN.Data';

SELECT

'$(B)' AS DB_Name,

*

FROM $(C);

NEXT

Thanks,

Przemek

6 Replies
fosuzuki
Partner - Specialist III
Partner - Specialist III

Hi Przemek,

what if you do something like this:

Table_1:

SELECT DB_Name FROM DB_Conf;

Table_2:

LET A = NoOfRows('Table_1');

FOR i=0 TO $(A)-1

LET B = peek('DB_Name',$(i),'DZ_Bazy');

LET C = peek('DB_Name',$(i),'DZ_Bazy')&'.CDN.Settings';

SELECT

'$(B)' AS DB_Name,

Dimension

FROM $(C);

NEXT

left join (Table1)

load *

resident Table2;

drop Table2;

Table_3:

LET A = NoOfRows('Table_1');

FOR i=0 TO $(A)-1

LET B = peek('DB_Name',$(i),'DZ_Bazy');

LET FlgDimension = peek('Dimension',$(i),'DZ_Bazy');

if($(FlgDimension) = 1) then

LET C = peek('DB_Name',$(i),'DZ_Bazy')&'.CDN.Data';

SELECT

'$(B)' AS DB_Name,

*

FROM $(C);

end if

NEXT

Not applicable
Author

Hi Fernando,

seems like a good path but the script doesn't read the below bit - the FOR...NEXT loop goes only for s only for if($(FlgDimension) = 1). Any ideas what to change?

Omitted bit:

LET C = peek('DB_Name',$(i),'DZ_Bazy')&'.CDN.Data';

SELECT

'$(B)' AS DB_Name,

*

FROM $(C);

Regards,

Przemek

fosuzuki
Partner - Specialist III
Partner - Specialist III

Hi Przemek,

I did not understand you.

In your original post, you said you needed to 'select only the DBs where Dimension = 1'. And in your last post, you said that 'the for..next loop goes only for if(...)'. The idea is that it will only run the selectto the DB when Dimension = 1. Isn't that what you need?

Regards,

Fernando

Not applicable
Author

I need to select DBs where Dimension = 1 to know from which DBs Data will be taken from. Script for Table_3 is supposed to select the Data from the given DBs but it omits the bit:

LET C = peek('DB_Name',$(i),'DZ_Bazy')&'.CDN.Data';

SELECT

'$(B)' AS DB_Name,

*

FROM $(C);

fosuzuki
Partner - Specialist III
Partner - Specialist III

Hi,

wht if you modify it to:

Table_1:

SELECT DB_Name FROM DB_Conf;

Table_2:

LET A = NoOfRows('Table_1');

FOR i=0 TO $(A)-1

LET B = peek('DB_Name',$(i),'DZ_Bazy');

LET C = peek('DB_Name',$(i),'DZ_Bazy')&'.CDN.Settings';

SELECT

'$(B)' AS DB_Name,

Dimension

FROM $(C);

NEXT

inner join (Table1)

load *

resident Table2 where Dimension = 1;

drop Table2;

Table_3:

LET A = NoOfRows('Table_1');

FOR i=0 TO $(A)-1

LET B = peek('DB_Name',$(i),'DZ_Bazy');

LET C = peek('DB_Name',$(i),'DZ_Bazy')&'.CDN.Data';

SELECT

'$(B)' AS DB_Name,

*

FROM $(C);

NEXT

The trick is that you first prepare the Table_1, with the Inner Join to filter only the DBs you need.

Not applicable
Author

Hi Fernando,

that's a very nice trick! The problem is that Dimension is not recognised when put in this place. I've tried to split the inner join part to "LOAD DB_Name, Dimension Where Dimension = 1" before Table_3 and "Inner Join (DZ_Bazy) LOAD * Resident DZ_Bazy; Drop Table Table_2;" after Table_3 but it keeps selecting both DBs...

Any idea now?