Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am using load script with resident. I need to use two tables as columns are from two tables.
HOw to do it.
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
Can you publish a sample file ?
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
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
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..
How to do this. Can you give me the query?
Thanks
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.
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;
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.