9 Replies Latest reply: Aug 16, 2015 1:33 AM by karthikeyan a r RSS

    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;

        • Re: Inner join Condition Problem
          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;

          • Re: Inner join Condition Problem
            Celambarasan Adhimulam

            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

                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

                    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
                  karthikeyan a r

                  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

                    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;