Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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;

1 Solution

Accepted Solutions
karthikeyan1504
Creator III
Creator III

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.

View solution in original post

9 Replies
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
Partner - Champion
Partner - Champion

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
Creator III
Creator III

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;

Not applicable
Author

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
Author

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
Creator III
Creator III

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;