Load Resident with Exists Issue

[Christopher_Lawrence]

Greetings All,

I am attempting a Resident Load with an Exist function to hopefully get rid of previous loaded records, but I seem to be doing something wrong.

I have two tables:

The first is called MontageView, which provides 21,000,000 results and is written this way:

MontageViewTemp:

LOAD

Src as MV.Src,

Text(MontageViewId) as MV.ID,

JobId as MV.JobID;

SELECT

"Src",

"MontageViewId",

"AssessmentId",

"JobId"

FROM "MHDW"."DM"."Dim_MO_MontageView_US" Where "MontageView_US_OID" > 0;

MontageView:

Load*,

Text([MV.JobID])&'|'&Text([MV.Src]) as Job.JobKeyText ,

Text([MV.ID])&'|'&Text([MV.Src]) as MV.MontageViewIDKeyText

Resident MontageViewTemp;

Drop Table MontageViewTemp;

The second table I have is called Assessment Order, which provides 1,700,000 lines. Its is written like this:

AssessmentOrderTemp:

LOAD Src as AO.Src,

AssessmentId as AO.AssessmentId,

ModifiedOn as AO.ModifiedOn,

StartedOn as AO.StartedOn,

CompletedOn as AO.CompletedOn,

InterviewId as AO.InterviewId;

SELECT "Src",

"AssessmentId",

"ModifiedOn",

"StartedOn",

"CompletedOn",

"InterviewId"

FROM "MHDW"."DM"."Dim_AS_Order_US" Where "AssessmentOrder_US_OID" > 0;

AssessmentOrder:

Load *,

Text (AO.AssessmentId)&'|'&Text (AO.Src) as AO.AssessmentKeyText,

Text (AO.InterviewId)&'|'&Text (AO.Src) as AO.MontageViewIDKeyText

Resident AssessmentOrderTemp;

Drop Table AssessmentOrderTemp;

I then wish to load only from the MontageView table that match results in the AssessmentOrder table, as I do not want to keep the 21 million results that are currently in the MontageView table. What I have written after creating these two tables is this:

MontageViewWithAssessments:

Load *

Resident MontageView

Where Exists('AO.MontageViewIDKeyText', MV.MontageViewIDKeyText);

Drop Table MontageView;

AO.MontageViewIDKeyText is the key located in the AssessmentOrder table and is used to link to the MontageView table through it’s key, MV.MontageViewIDKeyText. However, when I run this, the new MontageViewWithAssessments table provide me more than the 21 million results that the MontageView table had, opposed what should now be the 1.7 million results from the AssessmentOrder table. Am I using the Resident Load with the Exist function incorrectly?

On top of this, the script is not making a MontageViewWithAssessment table and dropping the MontageView Table. 

[Vegar]

Try this adjustment  to your exists script.

MontageViewWithAssessments:

Load *

Resident MontageView

Where Exists([AO.MontageViewIDKeyText], MV.MontageViewIDKeyText);

[marcus_sommer]

Identically table-structures will be auto-concatenated. This means you don't get a new table by:

new: load * resident old;

else the now loaded records are added to the existing table "old". If there is any new field within "new" it won't happens. Further you have the possibility to add a noconcatenate-statement to the new-load to prevent the auto-concatenating.

Beside this you don't need both temp-loads else the key's could be already created within the preceeding-parts of your sql-selects - maybe within another preceeding on top of them.

Depending on the fact if you need beside your exists-filtering also the other records from MontageView you may also save this resident-load because if you load the assessment-data first you could apply the where-exists already within the preceeding-part of the MontageView.

With it you will keep the script smaller and much more readable and you will of course save resources and run-times.

- Marcus

[Christopher_Lawrence]

How would I use the "Where Exists" if i chose to load AssessmentOrder first then MontageView? Am I able to use Resident Load in the SQL portion of MontageViewTemp? Or would i have to load the data in first with MontageViewTemp and then dictate the resident load in MontageView? Could you provide what that might look like?

[marcus_sommer]

Meant was something like this:

AssessmentOrder:

Load *,

Text (AO.AssessmentId)&'|'&Text (AO.Src) as AO.AssessmentKeyText,

Text (AO.InterviewId)&'|'&Text (AO.Src) as AO.MontageViewIDKeyText;

LOAD Src as AO.Src,

AssessmentId as AO.AssessmentId,

ModifiedOn as AO.ModifiedOn,

StartedOn as AO.StartedOn,

CompletedOn as AO.CompletedOn,

InterviewId as AO.InterviewId;

SELECT "Src", "AssessmentId", "ModifiedOn", "StartedOn", "CompletedOn",

"InterviewId"

FROM "MHDW"."DM"."Dim_AS_Order_US" Where "AssessmentOrder_US_OID" > 0;

MontageView:

Load *,

Text([MV.JobID])&'|'&Text([MV.Src]) as Job.JobKeyText ,

Text([MV.ID])&'|'&Text([MV.Src]) as MV.MontageViewIDKeyText

where Exists('AO.MontageViewIDKeyText', Text([MV.ID])&'|'&Text([MV.Src])) ;

LOAD Src as MV.Src, Text(MontageViewId) as MV.ID, JobId as MV.JobID;

SELECT

"Src", "MontageViewId",  "AssessmentId", "JobId"

FROM "MHDW"."DM"."Dim_MO_MontageView_US" Where "MontageView_US_OID" > 0;

- Marcus