topic Inner join Condition Problem in QlikView

Inner join Condition Problem

Sun, 16 Aug 2015 05:08:43 GMT

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;

Re: Inner join Condition Problem

Kushal_Chawda — Sun, 16 Aug 2015 05:22:10 GMT

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

CELAMBARASAN — Sun, 16 Aug 2015 05:23:02 GMT

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;

Re: Inner join Condition Problem

karthikeyan1504 — Sun, 16 Aug 2015 05:24:01 GMT

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

sunny_talwar — Sun, 16 Aug 2015 05:24:19 GMT

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

Sun, 16 Aug 2015 05:25:44 GMT

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

Sun, 16 Aug 2015 05:26:00 GMT

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

sunny_talwar — Sun, 16 Aug 2015 05:28:58 GMT

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

Kushal_Chawda — Sun, 16 Aug 2015 05:31:58 GMT

But why you want the resident load ?

Re: Inner join Condition Problem

karthikeyan1504 — Sun, 16 Aug 2015 05:33:50 GMT

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;