2 Replies Latest reply: Jul 5, 2011 10:43 AM by Marc Livingston RSS

    Selection issue with Description tables

      Does anyone have experience using Qlikview with a data source that uses "Description Tables"?

       

      I am using a SQL database for Epicor, where they like to have main tables that contain keys that you need to connect their description tables to.

       

      It also has issues because sometimes I need to pull a table to be able to access other information. EX. I was Part Class, but part class in in the part table not the order detail table. Have to add part table to access this information (50k of parts in part table)

       

      My main current issue is this:

       

      I want to be able to select territories by their descriptions, not thier id. when I pull in the description table and connect it, it seems to only change the selection for the directly linked table customer. It is not changing the orders selected (connected also to customer)

       

      Here is my Select statement

       

      SQL SELECT

          company,

          custnum,

          Month(convert(varchar, orderdate, 101)) as Month,

          Year(Orderdate) as Year,

          DATENAME(month, orderdate) as test,

          convert(varchar, orderdate, 101) as Converted,

          openorder,

          orderamt,

          orderdate,

          ordernum,

          requestdate

      FROM epicor904.dbo.orderhed

      where voidorder='0';

      SQL SELECT

          company,

          custnum,

          extpricedtl,

          linedesc,

          openline,

          orderline,

          ordernum,

          orderqty,

          partnum,

          prodcode

      FROM epicor904.dbo.orderdtl

      where voidline='0';

      SQL SELECT

          company,

          description,

          prodcode

      FROM epicor904.dbo.prodgrup;

      SQL SELECT

          company,

          custid,

          custnum,

          Month(convert(varchar, estdate, 101)) as EstMonth,

          Year(estdate) as EstYear,

          name,

          city,

          CustomerType,

          groupcode,

          state,

          country,

          salesrepcode,

          territoryid

      FROM epicor904.dbo.customer;

      SQL SELECT

          company,

          territoryid,

          territorydesc

      FROM epicor904.dbo.salester;

       

       

      Note:

      If I do not add the salester table then selection on territoryid from Orderhed table works fine. Once I add salester table this not longer works.

        • Selection issue with Description tables
          John Witherspoon

          I don't really see how you want these tables to link up, but in QlikView, if fields are to be treated as the same thing, they must be named the same thing.  So "openorder" is not the same as "ordernum", and "custnum" is not the same as "custid" and so on.  If these are intended to represent the same information, they needed to be named the same thing.

            • Re: Selection issue with Description tables

              They are not the same thing and are not intened to be linked.

               

              It seems to be linking everything correctly, all company fields on all tables are linked, then

               

              Custnum on Customer, Orderdtl, and Orderhed

              territoryid on Customer and salester

              ordernum on orderhed and orderdtl

              and

              prodcode on orderdtl and prodgrup.

               

              I am trying to be able to select the territory, and have a chart display the order amounts for that territory per year/Month, but for some reason when I add the salester table it is only affecting the Customer table and not changing the Order amounts found in orderdtl or orderhed.

               

              When I remove the salester table and select territoryid from Customer, then it DOES change the chart based on what I select.

               

               

               

              Attached is the Table link view.

               

              Message was edited by: marcsliving