Loading Data based upon values in an initial load.

[#1]

Hope someone can assist in this.

Initial load of a data set contains values I want to use to go and fetch a secondary data set. Not sure how to approach it.

Example.

Phase1:

LOAD "Entity ID"as [Entity ID];

  SQL SELECT "Entity ID"

FROM show_all_br where pid=1;

Then for each Entity ID in Phase1: I wish to concatenate the values into a string and  run another load for each of the given Entity ID contained in the string.

How?

Thanks in advance.

[#2]

Hi,

I think you mean something like this:

Phase2:

load distinct

     [Entity ID],

*

where exist([Entity ID],[Entity ID]) ;

  SQL SELECT "Entity ID"

FROM show_all_br where pid=1;

Qlikview is now looking if the string already exists.

Succes!

Halmar

[#3]

Thanks for the reply Halmar, I need to describe the problem a little clearer I think.

The Query needs to take 3 forms..

1) Phase 1 retrieve  the Entity ID,

2) Phase 2 For each Entity ID retrieve all the Business Rule ID's

3) Phase 3 For each Business Rule ID retrieve all the failing records.

The database is an associative database not pure SQL, but I can use Select (column names) or * and Where clauses but only for a specific set of values .. these are the Entity ID and the Business Rules ID (in this instance)

So Can I retrieve the relevant ID's into memory and then use the In memory ID (via a variable?) to then call the next query? or is there a simpler way of doing it?

So the Logical view is :

Entity_Load:

LOAD DISTINCT "Entity ID";

SQL SELECT "Entity ID",

FROM "show_all_br" where pid=1;

Business_Rule_Load:

For each EntityID

  LOAD "Bus ID";

  SQL SELECT "Bus ID",

  FROM "BusRule" where EID="Entity ID" (variable?)

Next EntityID

Failing Row Load:

For each BusID 

LOAD *;

  SQL SELECT *,

  FROM "FailingRows" where EID="Entity ID" (variable?) & ebr = BusID

Next BusID

Hope this is a little clearer?

[#4]

Do it like this:

Entity_Load:

LOAD DISTINCT "Entity ID";

SQL SELECT "Entity ID",

FROM "show_all_br" where pid=1;

left join

LOAD

     EID     as Entity ID

     ebr      as "Bus ID",

     *;

  SQL SELECT *,

  FROM "FailingRows"

You see that you delete the second step.

Halmar

[#5]

Thanks again, still not quite right .. In order to run the second query to fetch the failing rows I must pass in the EID and Bus ID together .. so I need to loop through them.

Query so far ... with the data in a view below

The yellow column is the Entity ID, the Green in the Bus ID .. now I need to pass th query in to fetch the failing rows ..

such as ..

Load *;

Slect *

from Failing_Br where eid=Entity ID and brid = Bus ID;

[#6]

Hi,

You can do this bij using the two fields as a key.

Like:

LoadTemp:

LOAD DISTINCT "Entity ID";

SQL SELECT "Entity ID",

FROM "show_all_br" where pid=1;

left join

LOAD

EID as "Entity ID"

"Bus ID";

  SQL SELECT "Bus ID",

  FROM "BusRule"

LoadTemp1:

load

     "Entity ID" & '|' "Bus ID"     as Key

resident LoadTemp;

drop table LoadTemp1;

left join

LOAD

     EID & '|' & ebr          as Key,

     *;

  SQL SELECT *,

  FROM "FailingRows" ;

[#7]

I cant use that Halmar.. the where Clause has to contain both Entity ID and Bus ID or it will not run..

Such as below ...

LOAD *;

SQL SELECT *

FROM "view_br_fail" where eid=8 and ebr=4;

So I need to iterate throught each Entity ID(eid) and then Each Bus ID (ebr) it contains