Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Tags (1)
6 Replies
fosuzuki
Valued Contributor II

How to select data upon a dimension not available in the dataset?

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

How to select data upon a dimension not available in the dataset?

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
Valued Contributor II

How to select data upon a dimension not available in the dataset?

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

How to select data upon a dimension not available in the dataset?

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
Valued Contributor II

How to select data upon a dimension not available in the dataset?

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

How to select data upon a dimension not available in the dataset?

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?

Community Browser