Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Inner join Condition Problem

Hi Dears .

i have one problem in inner join

Please Help me.

i have Two Tables (DimDepartmentGroup,X)

I would like to review more criteria but how?write in one line

DimDepartmentGroup.IsDeleted=0 And Information.CancelFlag=1 And DimDepartmentGroup.ParentDepartmentGroupKey is not null

DimDepartmentGroup:

LOAD DepartmentGroupKey,

    ParentDepartmentGroupKey,

    DepartmentGroupName,

    IsDeleted;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.DimDepartmentGroup;

inner join

Information:

LOAD Id,

    FName,

    LName,

    DepartmentGroupKey,

    CancelFlag;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.X;

Tags (1)
1 Solution

Accepted Solutions
karthikeyan1504
Contributor III

Re: Inner join Condition Problem

Hi,


Please view the below code in traditional way,


DimDepartmentGroup:

LOAD DepartmentGroupKey,

    ParentDepartmentGroupKey,

    DepartmentGroupName,

    IsDeleted;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.DimDepartmentGroup

where IsDeleted=0 and ParentDepartmentGroupKey is not null;

inner join

Information:

LOAD Id,

    FName,

    LName,

    DepartmentGroupKey,

    CancelFlag;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.X

where CancelFlag=1;

But I prefer you change your script in qlikview form as mentioned below,

DimDepartmentGroup:

LOAD DepartmentGroupKey,

    ParentDepartmentGroupKey,

    DepartmentGroupName,

    IsDeleted;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.DimDepartmentGroup;

inner join

LOAD Id,

    FName,

    LName,

    DepartmentGroupKey,

    CancelFlag;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.X;

FinalTablel:

Load *

Resident DimDepartmentGroup

where IsDeleted=0 and not isnull( ParentDepartmentGroupKey)

and CancelFlag=1;

Drop Table DimDepartmentGroup;

Hope this helps!!!

Warm Regards,

Karthikeyan.

9 Replies

Re: Inner join Condition Problem

may be try this

DimDepartmentGroup:

LOAD DepartmentGroupKey,

    ParentDepartmentGroupKey,

    DepartmentGroupName,

    IsDeleted

where IsDeleted=0 and len(trim(ParentDepartmentGroupKey ))>0;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.DimDepartmentGroup;

inner join

Information:

LOAD Id,

    FName,

    LName,

    DepartmentGroupKey,

    CancelFlag

where CancelFlag=1;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.X;

Re: Inner join Condition Problem

Try this

DimDepartmentGroup:

LOAD DepartmentGroupKey,

    ParentDepartmentGroupKey,

    DepartmentGroupName,

    IsDeleted

Where IsDeleted=0 AND not ISNULL(ParentDepartmentGroupKey);

SQL SELECT *

FROM AdventureWorksDW2012.dbo.DimDepartmentGroup;

inner join

Information:

LOAD Id,

    FName,

    LName,

    DepartmentGroupKey,

    CancelFlag

Where CancelFlag=1;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.X;

karthikeyan1504
Contributor III

Re: Inner join Condition Problem

Hi,


Please view the below code in traditional way,


DimDepartmentGroup:

LOAD DepartmentGroupKey,

    ParentDepartmentGroupKey,

    DepartmentGroupName,

    IsDeleted;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.DimDepartmentGroup

where IsDeleted=0 and ParentDepartmentGroupKey is not null;

inner join

Information:

LOAD Id,

    FName,

    LName,

    DepartmentGroupKey,

    CancelFlag;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.X

where CancelFlag=1;

But I prefer you change your script in qlikview form as mentioned below,

DimDepartmentGroup:

LOAD DepartmentGroupKey,

    ParentDepartmentGroupKey,

    DepartmentGroupName,

    IsDeleted;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.DimDepartmentGroup;

inner join

LOAD Id,

    FName,

    LName,

    DepartmentGroupKey,

    CancelFlag;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.X;

FinalTablel:

Load *

Resident DimDepartmentGroup

where IsDeleted=0 and not isnull( ParentDepartmentGroupKey)

and CancelFlag=1;

Drop Table DimDepartmentGroup;

Hope this helps!!!

Warm Regards,

Karthikeyan.

Re: Inner join Condition Problem

Try this:

DimDepartmentGroup:

LOAD DepartmentGroupKey,

    ParentDepartmentGroupKey,

    DepartmentGroupName,

    IsDeleted

Where IsDeleted = 0 and Len(Trim(ParentDepartmentGroupKey)) > 0;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.DimDepartmentGroup;

inner join

Information:

LOAD Id,

    FName,

    LName,

    DepartmentGroupKey,

    CancelFlag

Where CancelFlag = 1;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.X;

Not applicable

Re: Inner join Condition Problem

Thanks

Can we wrote a line and created a new table

For example :

     i want load All Data Without Condition.

After Create New Table Look Like (Resident) Then I applied condition؟

Not applicable

Re: Inner join Condition Problem

Thanks

Can we wrote a line and created a new table

For example :

     i want load All Data Without Condition.

After Create New Table Look Like (Resident) Then I applied condition؟

Re: Inner join Condition Problem

May be this:

DimDepartmentGroup:

LOAD DepartmentGroupKey,

    ParentDepartmentGroupKey,

    DepartmentGroupName,

    IsDeleted;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.DimDepartmentGroup;

Information:

LOAD Id,

    FName,

    LName,

    DepartmentGroupKey,

    CancelFlag;

SQL SELECT *

FROM AdventureWorksDW2012.dbo.X;


Table:

NoConcatenate

LOAD *

Resident DimDepartmentGroup

Where IsDeleted = 0 and Len(Trim(ParentDepartmentGroupKey)) > 0;


Inner Join (Table)

LOAD *

Resident Information

Where CancelFlag = 1;


Re: Inner join Condition Problem

But why you want the resident load ?

karthikeyan1504
Contributor III

Re: Inner join Condition Problem

Looks like I have missed one condition in my script...

Please include 'Nocancatenate' in resident load statement.

FinalTablel:

nocancatenate

Load *

Resident DimDepartmentGroup

where IsDeleted=0 and not isnull( ParentDepartmentGroupKey)

and CancelFlag=1;

Community Browser