resident with two tables

[#1]

Hi,

I am using load script with resident. I need to use two tables as columns are from two tables.

HOw to do it.

[Anonymous]

Hey,

If the two tables are having same columns you can simple load using resident load one after another using concatenate. So that it will work as union in oracle/sql.

If the two tables are having 2 different columns and if you can just to pull the columns from two tables based on a possible join.

If the two tables not have any possible common columns you cannot merge them.

Hope this helps.

BR,

Chinna

[marwen_garwachi]

Can you publish a sample file ?

[#4]

Hi,

Thanks for quick reply.

Please find the script that I am using.

ValidBaselinetable:

Load

UID, sum(if(Result=3,1,0))/14 AS [Valid Baseline Compliance (%)]

Resident CheckGraph

group by UID;

BIOGraph:

Load UID,

     'Active Schedules' as Category,

     if( [Project Status / State] ='Active',1,0) as value

Resident ProjectTracker;

Concatenate (BIOGraph)

Load UID,

     '100% Valid Baseline Compliant' as Category,

     if([Valid Baseline]='Yes' and [Valid Baseline Compliance (%)]='1',1,0) as value

Resident ProjectTracker;

Here [Valid Baseline] is part of projecttracker table but [Valid Baseline Compliance (%)] is part of ValidBaselinetable table, So while reloading it is giving error field not found.

Can you provide me the script for that.

Thanks

[Anonymous]

Hi Amit Sharma ,

U see you [Valid Baseline Compliance (%)] field is not in Project tracker that is why you having that error

try to join the table where [Valid Baseline Compliance (%)] is in to your  ProjectTracker before the last resident load

[Anonymous]

ValidBaselinetable:

Load

UID, sum(if(Result=3,1,0))/14 AS [Valid Baseline Compliance (%)]

Resident CheckGraph

group by UID;

BIOGraph:

Load UID,

     'Active Schedules' as Category,

     if( [Project Status / State] ='Active',1,0) as value

Resident ProjectTracker;

join (BIOGraph)

Load UID,

     '100% Valid Baseline Compliant' as Category,

     if([Valid Baseline]='Yes' and [Valid Baseline Compliance (%)]='1',1,0) as value

Resident ValidBaselinetable;

Try this..

[#7]

How to do this. Can you give me the query?

Thanks

[#8]

I tried, but giving error

Field not found - <Valid Baseline>

join (BIOGraph)

Load UID,

     '100% Valid Baseline Compliant' as Category,

     if([Valid Baseline]='Yes' and [Valid Baseline Compliance (%)]='1',1,0) as value

Resident ValidBaselinetable

because valid baseline is part of projecttracker table.

[Anonymous]

BIOGraphTemp:

Load UID,

     'Active Schedules' as Category,

     if( [Project Status / State] ='Active',1,0) as value

,[Valid Baseline]

Resident ProjectTracker ;

left join(BIOGraph)  // use proper join according to data

ValidBaselinetable:

Load

UID, sum(if(Result=3,1,0))/14 AS [Valid Baseline Compliance (%)]

Resident CheckGraph

group by UID;

BIOGraph:

load

Category,

if([Valid Baseline]='Yes' and [Valid Baseline Compliance (%)]='1',1,0) as value

Resident BIOGraphTemp;

Concatenate (BIOGraph)

Load UID,

      Category,

      value

Resident ProjectTracker;

[Anonymous]

Hi,

here it is...

ValidBaselinetable:

Load

UID, sum(if(Result=3,1,0))/14 AS [Valid Baseline Compliance (%)]

Resident CheckGraph

group by UID;

BIOGraphtmp:

Noconcatenate

Load UID,

    'Active Schedules' as Category,

    if( [Project Status / State] ='Active',1,0) as value,

[Valid Baseline]

Resident ProjectTracker;

join//(ValidBaselinetable)

Load UID, [Valid Baseline Compliance (%)] resident ValidBaselinetable;

BIOGraph:

Noconcatenate

Load UID,

  Category,

    value

Resident BioGraphtmp where Category ='Active Schedules';

Concatenate (BIOGraph)

Load UID,

    '100% Valid Baseline Compliant' as Category,

    if([Valid Baseline]='Yes' and [Valid Baseline Compliance (%)]='1',1,0) as value

Resident BioGraphtmp;

Drop table BioGraphtmp;

You may need to remove the Projecttracker and ValidBaseline tables inorder to avoid synthetic keys as in BIOGrpah tables also we are representing the UID column with same name.

Hope this helps.

Best regards,

Chinna

PFA Sample example for resident from two tables for your reference.