Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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.

View solution in original post

2 Replies
johnw
Champion III
Champion III

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.

Not applicable
Author

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