topic Re: resident with two tables in QlikView

resident with two tables

Tue, 25 Nov 2014 13:20:19 GMT

Hi,

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

HOw to do it.

Re: resident with two tables

Anonymous — Tue, 25 Nov 2014 13:28:02 GMT

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

Re: resident with two tables

marwen_garwachi — Tue, 25 Nov 2014 13:32:07 GMT

Can you publish a sample file ?

Re: resident with two tables

Tue, 25 Nov 2014 13:35:10 GMT

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

Re: resident with two tables

Anonymous — Tue, 25 Nov 2014 13:41:30 GMT

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

Re: resident with two tables

Anonymous — Tue, 25 Nov 2014 13:47:44 GMT

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..

Re: resident with two tables

Tue, 25 Nov 2014 13:48:35 GMT

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

Thanks

Re: resident with two tables

Tue, 25 Nov 2014 13:52:09 GMT

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.

Re: resident with two tables

Anonymous — Tue, 25 Nov 2014 14:18:39 GMT

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;

Re: Re: resident with two tables

Anonymous — Wed, 26 Nov 2014 06:40:58 GMT

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.

Re: resident with two tables

sujeetsingh — Wed, 26 Nov 2014 06:52:05 GMT

Amit first come in more words please.

Explain the table fields and key to link