2 Replies Latest reply: Apr 26, 2012 3:12 PM by prateekb RSS

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

      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;

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

          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.

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

              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.