Can someone look at my Script/ Issue with data, or... - Qlik Community

Report Inappropriate Content · ‎2011-07-20

Can someone take a look at my Select statement and let me know if it is correct? I seem to be having issues validation the numbers with our live system. I am currently using the Personal trial edition. Numbers match up until I start selecting items. How could I use joins to make this select statement only show based on the head tables?

Example: Invched contains the flag for invoicetype and if it is Posted. I want only invoices where invoicetype='SHP' and Posted=1. This only is working if I make selection based on the invched table. I am also pulling data from invcdtl, which I need the same to apply to, but these flags do not exist at this level. This is also the case for most of the other tables. I want this information that is pertaining directly to orders, but with how qlikview is set up it is pulling all data from every table.

Other example: We are able to link jobs in with orders, or we can create jobs without orders. In this example I only want jobs that have orders linked in. To get the job costs, I have to link through several tables, where it does not have a flag telling it if it has an order.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;-$#,##0.00';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=epro;Initial Catalog=epicor904;Data Source=EPSQL;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MARC;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is SNNZRSBOSbbSD);

LOAD

company & '-' & custnum as companycust,

company & '-' & ordernum as companyordernum,

company & '-' & ordernum as companyord,

company,

Month(orderdate) as Monthord,

Year(orderdate) as Yearord,

Date(orderdate,'MM/DD/YYYY') as OrdDate,

if(YEAR(orderdate)=Year(today()),orderamt) as Currentyear,

if(YEAR(orderdate)=(Year(today())-1),orderamt/.8) as Goal,

'Q' & Ceil(Month(orderdate)/3) as OrderQuarter,

openorder,

orderamt,

If(orderamt<25000,'1. Under 25K',If((orderamt>=25000 and orderamt<50000),'2. 25K–50K',If((orderamt>=50000 and orderamt<100000),'3. 50K to 100K',If((orderamt>=100000 and orderamt<200000),'4. 100K to 200K',If((orderamt>=200000 and orderamt<300000),'5. 200K to 300K',If((orderamt>=300000 and orderamt<400000),'6. 300K to 400K',If((orderamt>=400000 and orderamt<500000),'7. 400K to 500K',if(orderamt>=500000,'8. Over 500K'))))))))as OrderLvl,

ordernum,

requestdate;

SQL SELECT

company,

custnum,

orderdate,

openorder,

orderamt,

ordernum,

requestdate

FROM epicor904.dbo.orderhed

where voidorder='0';

Load

company & '-' & ordernum as companyord,

company & '-' & prodcode as companyprod,

company & '-' & partnum as companypart,

company & '-' & ordernum & '-' & orderline as companyorderline,

company & '-' & ordernum & '-' & orderline as Companyrel,

company as orddtlcompany,

(docextpricedtl-docdiscount) as extpricedtl,

docdiscount,

If(openline=1,extpricedtl) as OpenAmt,

If(openline=0,extpricedtl) as ClosedAmt,

linedesc,

openline,

orderline,

orderqty,

partnum,

prodcode;

SQL SELECT

company,

extpricedtl,

docextpricedtl,

docdiscount,

linedesc,

openline,

orderline,

ordernum,

orderqty,

partnum,

prodcode

FROM epicor904.dbo.orderdtl

where voidline='0';

LOAD

buytoorder,

company & '-' & ordernum & '-' & orderline as Companyrel,

company & '-' & ordernum & '-' & orderline & '-' & orderrelnum as companyshipord,

company & '-' & ordernum & '-' & orderline & '-' & orderrelnum as companyjobprod,

company as orderrelcompany,

make,

orderrelnum,

reqdate as RelReqby,

sellingreqqty as ReleaseQty;

SQL SELECT buytoorder,

company,

make,

orderline,

ordernum,

orderrelnum,

reqdate,

sellingreqqty

FROM epicor904.dbo.orderrel

WHERE voidrelease=0;

LOAD

company & '-' & partnum as companypart,

company & '-' & classid as companyclass;

SQL SELECT

company,

partnum,

classid

FROM epicor904.dbo.part;

LOAD

company & '-' & classid as companyclass,

description as Class;

SQL SELECT

company,

classid,

description

FROM epicor904.dbo.partclass;

LOAD

company & '-' & prodcode as companyprod,

description as Product;

SQL SELECT

company,

description,

prodcode

FROM epicor904.dbo.prodgrup;

LOAD

company & '-' & custnum as companycust,

company & '-' & territoryid as companyter,

company & '-' & groupcode as companygroup,

custnum,

custid,

Month(estdate) as EstMonth,

Year(estdate) as EstYear,

Date(estdate) as EstDate,

name,

city,

customertype,

groupcode,

state,

country,

salesrepcode;

SQL SELECT

company,

custid,

custnum,

estdate,

name,

city,

customertype,

groupcode,

state,

country,

salesrepcode,

territoryid

FROM epicor904.dbo.customer;

LOAD

company & '-' & territoryid as companyter,

territorydesc;

SQL SELECT

company,

territoryid,

territorydesc

FROM epicor904.dbo.salester;

LOAD

company & '-' & schedcode as companysch,

description as SchedulePriority;

SQL SELECT

company,

schedcode,

description

FROM epicor904.dbo.SchedPri;

LOAD

company & '-' & groupcode as companygroup,

groupdesc as CustomerGroup;

SQL SELECT company,

groupcode,

groupdesc

FROM epicor904.dbo.custgrup;

LOAD

if(isnull(invoicenum),'Not Invoiced','Invoiced') as Status,

applydate,

closeddate,

company & '-' & invoicenum as companyinv,

company as invcomp,

docinvoiceamt,

docinvoicebal,

duedate,

invoiceamt,

invoicebal,

invoicedate,

Month(invoicedate)as invoicemonth,

Year(invoicedate) as invoiceyear,

Month(invoicedate) & '-' & Year(invoicedate) as invoicemonthyear,

'Q' & Ceil(Month(invoicedate)/3) as Quarter,

Dual(Year(invoicedate) & '-Q' & Ceil(Month(invoicedate)/3), Year(invoicedate) & Ceil(Month(invoicedate)/3)) as YearQtr,

invoicenum,

posted,

shipdate as invoiceshipdate,

invoicetype;

SQL SELECT applydate,

closeddate,

company,

docinvoiceamt,

docinvoicebal,

duedate,

invoiceamt,

invoicebal,

invoicedate,

invoicenum,

ordernum,

posted,

shipdate,

invoicetype

FROM epicor904.dbo.invchead

WHERE (posted=1 and invoicetype='shp');

LOAD

company & '-' & invoicenum & '-' & invoiceline as companymsc,

company & '-' & ordernum & '-' & orderline & '-' & orderrelnum & '-' & packnum & '-' & packline as companyshipinv,

company & '-' & invoicenum as companyinv,

company as companyinvcdtl,

discount,

discountpercent,

docextprice,

docunitprice,

extprice,

invoiceline,

packnum as invpack,

sellingorderqty,

sellingshipqty;

SQL SELECT company,

discount,

discountpercent,

docextprice,

docunitprice,

extprice,

invoiceline,

invoicenum,

orderline,

ordernum,

orderrelnum,

packnum,

packline,

sellingorderqty,

sellingshipqty

FROM epicor904.dbo.invcdtl;

LOAD

company & '-' & invoicenum & '-' & invoiceline as companymsc,

misccode,

description as MiscDescription,

miscamt;

SQL SELECT company,

invoicenum,

invoiceline,

misccode,

description,

miscamt

FROM epicor904.dbo.invcmisc;

LOAD

company & '-' & shipviacode as companyshipvia,

company & '-' & packnum as companypack,

deliverytype,

invoiced as Packinvoiced,

packnum,

readytoinvoice,

shipcomment,

shipdate,

'Q' & Ceil(Month(shipdate)/3) as ShipQuarter,

Month(shipdate) as Shipmonth,

Year(shipdate) as Shipyear,

shipstatus,

shipviacode;

SQL SELECT company,

deliverytype,

invoiced,

packnum,

readytoinvoice,

shipcomment,

shipdate,

shipstatus,

shipviacode

FROM epicor904.dbo.shiphead;

LOAD

company & '-' & packnum as companypack,

company & '-' & ordernum & '-' & orderline & '-' & orderrelnum & '-' & packnum & '-' & packline as companyshipinv,

company & '-' & ordernum & '-' & orderline & '-' & orderrelnum as companyshipord,

packline,

invoiced as Lineinvoiced;

SQL SELECT

company,

invoiced,

orderline,

ordernum,

orderrelnum,

packline,

packnum

FROM epicor904.dbo.shipdtl;

LOAD

company & '-' & shipviacode as companyshipvia,

description as ShippedVia;

SQL SELECT company,

description,

shipviacode

FROM epicor904.dbo.shipvia;

JobProd:

LOAD

company & '-' & ordernum & '-' & orderline & '-' & orderrelnum as companyjobprod,

company & '-' & jobnum as companyjob,

company as prodcompany,

ordernum as joborder;

SQL SELECT

company,

jobnum,

ordernum,

orderline,

orderrelnum

FROM epicor904.dbo.jobprod

WHERE ordernum<>0;

Right Join (JobProd)

LOAD

company & '-' & jobnum as companyjob,

company & '-' & schedcode as companysch,

company & '-' & jobnum as companyassm,

company as jhedcompany,

Date(createdate) as Createdate,

Month(createdate) as Monthjobcreate,

Year(createdate) as Yearjobcreate,

'Q' & Ceil(Month(createdate)/3) as QuarterJobcreate;

SQL SELECT

company,

jobnum,

createdate,

schedcode

FROM epicor904.dbo.jobhead;

LOAD

assemblyseq,

company & '-' & jobnum as companyassm,

company as jobcompany,

((tlaburdencost + tlalaborcost + tlamaterialburcost + tlamaterialcost + tlamateriallabcost + tlamaterialmtlburcost + tlamaterialmtlcost + tlamaterialsubcost + tlamtlburcost + tlasubcontractcost)+

(llaburdencost + llalaborcost + llamaterialburcost + llamaterialcost + llamateriallabcost + llamaterialmtlburcost + llamaterialmtlcost + llamaterialsubcost + llamtlburcost + llasubcontractcost)) as Totalcost,

llaburdencost,

llalaborcost,

llamaterialburcost,

llamaterialcost,

llamateriallabcost,

llamaterialmtlburcost,

llamaterialmtlcost,

llamaterialsubcost,

llamtlburcost,

llaprodhours,

llasetuphours,

llasubcontractcost,

lleburdencost,

llelaborcost,

llematerialcost,

llemtlburcost,

lleprodhours,

llesetuphours,

llesubcontractcost,

tlaburdencost,

tlalaborcost,

tlamaterialburcost,

tlamaterialcost,

tlamateriallabcost,

tlamaterialmtlburcost,

tlamaterialmtlcost,

tlamaterialsubcost,

tlamtlburcost,

tlaprodhours,

tlasetuphours,

tlasubcontractcost,

tleburdencost,

tlelaborcost,

tlematerialcost,

tlemtlburcost,

tleprodhours,

tlesetuphours,

tlesubcontractcost;

SQL SELECT

assemblyseq,

company,

jobnum,

llaburdencost,

llalaborcost,

llamaterialburcost,

llamaterialcost,

llamateriallabcost,

llamaterialmtlburcost,

llamaterialmtlcost,

llamaterialsubcost,

llamtlburcost,

llaprodhours,

llasetuphours,

llasubcontractcost,

lleburdencost,

llelaborcost,

llematerialcost,

llemtlburcost,

lleprodhours,

llesetuphours,

llesubcontractcost,

tlaburdencost,

tlalaborcost,

tlamaterialburcost,

tlamaterialcost,

tlamateriallabcost,

tlamaterialmtlburcost,

tlamaterialmtlcost,

tlamaterialsubcost,

tlamtlburcost,

tlaprodhours,

tlasetuphours,

tlasubcontractcost,

tleburdencost,

tlelaborcost,

tlematerialcost,

tlemtlburcost,

tleprodhours,

tlesetuphours,

tlesubcontractcost

FROM epicor904.dbo.jobasmbl

WHERE assemblyseq=0;

Report Inappropriate Content · ‎2011-07-20

Basically my issue if it is not clear in the above is this:

I need the way the tables that are linked to stay the same due to how the tables link together. I however need some of these tables to depend on criteria set in another table.

ie: invcdtl only for invched records that are posted, and have invoicetype=SHP. Currently pulling all invcdtl records even if they are not posted or equal to this invoicetype.

I also want only jobs that are linked with order releases. There are several job tables, and to get the costs I need to link several other tables in.

ie: jobprod table must have an order linked, and i do not want to see any other jobs that do not have orders. Works for selection in jobprod table, but if selection is only on the job table, it ignores this link/ criteria set.

Report Inappropriate Content · ‎2012-04-26

Dude that is a long list of table:

I ll give you a rough idea:

Table A:

Load StoreID,

Store_Description

From countrywidestores.csv

where Country='India';

Table B:

Left Join

Load StoreID,

Manager_Name

From countrywidemanager.csv;

What happens here is only storeIDs from India get loaded. And the managers for only the LOADED STORES get loaded.

Hope this is what you wanted.

Can someone look at my Script/ Issue with data, or with joins