Inner join Condition Problem

[#1]

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;

[Kushal_Chawda]

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;

[CELAMBARASAN]

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 · Accepted solution]

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.

[sunny_talwar]

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;

[#6]

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؟

[#7]

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؟

[sunny_talwar]

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;

[Kushal_Chawda]

But why you want the resident load ?

[karthikeyan1504]

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;