3 Replies Latest reply: Nov 8, 2017 8:43 AM by omar bensalem RSS

    Regarding Joins in the Load Editor

    ramesh konda

      Hi,

       

      I have 3 Tables which loaded in my Script Editor.

       

      Table1 with (TerritoryID,Area) with ( TerritoryID 55 Distinct Values )

      Table2 with ( TerritoryID, TerritoryStae, PostCode ) with (TerritoryID 62 Distinct Values)

      Table3 with (TerritoryID,Cust First Name,Cust Last Name,Sales Rep) with (TerritoryID 130 DIstinct Values )

       

      I Made all the Tables Join with TerritoryID Column in the Load editor.

       

      When I Create a Table report in the QlikSense, When I pull a Dimension column from TerritoryID (Expression editor fx) it is pulling all the 130 Dimension values into the Table report.

       

      Issue1:

      So i removed it and pull the column from Table1.TerritoryID (Expression editor fx) which im expecting only the 55 Distinct TerritoryID values, again it is showing the same 130 Distinct values even i'm pulling from Table1.TerritoryID.

       

      Issue2:

      Table1 with (TerritoryID,TerritoryArea ) with ( TerritoryID 55 Distinct Values )

      Table4 with (TerritoryID,State) with (TerritoryID 42 Distinct Values )     all these 42 values exists in Table1

       

      So when i Join The table as below im getting

       

      [Table2]:

      LOAD

          TerritoryID,

          state

      FROM .....Table2.xlsx (ooxml, embedded labels, table is Sheet);

      [Table1]:

      LOAD

          Distinct  TerritoryID,

          Area

      FROM ......... (qvd);;

       

      Join ([Table1])

      LOAD state as 'Terr State'

      Resident [Table2];

       

      When i ran the above in Table1

      Im getting TerritoryID,Area,'Terr State'  with 225 Records. where i'm expecting 55 records.

        • Re: Regarding Joins in the Load Editor
          omar bensalem

          You want only the 55 territoryID that exist i,n table 1?

           

          Table1:

          load * from source1:

           

          left join(Table1)

           

          table2:

          load * from source2;

           

          left join(Table1)

           

          Table3:

          load * from source3;

          • Re: Regarding Joins in the Load Editor
            ramesh konda

            Omar Thanks for the Response.

             

            Ya It should be when i pull Table1.TerritoryID from ( Expression editor ) it should show only 55 Values )

            Ya It should be when i pull Table2.TerritoryID from ( Expression editor ) it should show only 62 Values )

            Ya It should be when i pull Table3.TerritoryID from ( Expression editor ) it should show only 130 Values )

             

            as We know when we can see dropdown list we can able to see only one TerritoryID , when i pull it in the table report, I'm seeing all the 130 Values.

              • Re: Regarding Joins in the Load Editor
                omar bensalem

                In that case ;add a flag in the 3 tables:

                 

                I mean:

                 

                Table1:

                load *,

                'table1' as Flag

                from source1:

                 

                 

                table2:

                load *,

                'table2' as Flag from source2;

                 

                 

                Table3:

                load *,

                'table3' as Flag from source3;

                 

                and in your expression; if want to only work with fields from table1; you add this Flage,

                Example:

                if(Flag='table1', TerritoryID, null()) as a dimension?

                Try it