Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.
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;
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;
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.
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;
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؟
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؟
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;
But why you want the resident load ?
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;