topic Loading Data based upon values in an initial load. in QlikView

Loading Data based upon values in an initial load.

Fri, 09 Dec 2011 09:38:58 GMT

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.

Loading Data based upon values in an initial load.

Fri, 09 Dec 2011 11:28:53 GMT

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

Loading Data based upon values in an initial load.

Fri, 09 Dec 2011 11:44:43 GMT

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?

Loading Data based upon values in an initial load.

Fri, 09 Dec 2011 13:56:53 GMT

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

Loading Data based upon values in an initial load.

Fri, 09 Dec 2011 14:41:04 GMT

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;

Loading Data based upon values in an initial load.

Fri, 09 Dec 2011 16:18:04 GMT

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" ;

Loading Data based upon values in an initial load.

Fri, 09 Dec 2011 17:52:42 GMT

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