Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

resident with two tables

Hi,

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

HOw to do it.

10 Replies
chinnakatikisg
Valued Contributor

Re: resident with two tables

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
Contributor II

Re: resident with two tables

Can you publish a sample file ?

Not applicable

Re: resident with two tables

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

fahuruba
Contributor

Re: resident with two tables

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

chinnakatikisg
Valued Contributor

Re: resident with two tables

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

Not applicable

Re: resident with two tables

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

Thanks

Not applicable

Re: resident with two tables

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.

fahuruba
Contributor

Re: resident with two tables

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;

chinnakatikisg
Valued Contributor

Re: Re: resident with two tables

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.

Community Browser