2 Replies Latest reply: May 15, 2013 11:41 AM by Laurens Schepers RSS

    Pullling data from multiple datasources [13] in simplified script

      Hi,

      So, first let me tell you what I'm after.  I'm pulling together detailed invoice records from 13 datasources, using the same SQL statement.  After that, I need to group them all so that there is some organization to them.

       

      My questions:

      1) Using that SQL statement, how do I iterate through my 13 datasources so that I can gather this data.  Right now, I am only looking at 2 datasources, using two copies of the same statement explicitly stating the datasources in the FROM clause:

       

       

       

      ODBC CONNECT32 TO [GPSProd_11;DBQ=GPSPROD11];

      ODBC CONNECT32 TO [GPSProd_07;DBQ=GPSPROD07];

       

       

       

      let varStateDate = '2013-01-01';

      let varEndDate =  '2013-01-31';

       

       

       

      SQL

       

      SELECT  

                           shipperCity.state PU_state,

               dropCity.state DR_state,

               p.pronumuk as trips,

               ih.BilledMiles as Total_Miles,

               ih.totalweight as Total_Wgt,

               ih.totalcharges as Total_Charges,

               p.shipdate

             //  prh.paytotal as Total_Pay

       

       

      FROM    

                           GPSPROD07.proheader p,

                           GPSPROD07.invoiceheader ih,

                           GPSPROD07.city shipperCity,

                           GPSPROD07.city dropCity

                          // GPSPROD07.Payrecordheader prh,

      WHERE     

                    p.MABCode = 2610

      AND     p.prostatus in (120,130)

      AND     p.billingpro = ih.pronumuk

      AND     ih.superceded = 'N'

      AND              ih.invoiceletter = 'A'

      AND             p.fromcity = shipperCity.code

      AND             p.tocity = dropCity.code

      AND              p.shipdate >= '$(varStateDate)'

      AND     p.deliverydate <= '$(varEndDate)'

      ;

       

       

       

       

       

       

      SQL

      SELECT  

                           shipperCity.state PU_state,

               dropCity.state DR_state,

               p.pronumuk as trips,

               ih.BilledMiles as Total_Miles,

               ih.totalweight as Total_Wgt,

               ih.totalcharges as Total_Charges,

               p.shipdate

             //  prh.paytotal as Total_Pay

       

       

      FROM    

                           GPSPROD11.proheader p,

                           GPSPROD11.invoiceheader ih,

                           GPSPROD11.city shipperCity,

                           GPSPROD11.city dropCity

                          // GPSPROD07.Payrecordheader prh,

      WHERE     

                    p.MABCode = 610

      AND     p.prostatus in (120,130)

      AND     p.billingpro = ih.pronumuk

      AND     ih.superceded = 'N'

      AND              ih.invoiceletter = 'A'

      AND             p.fromcity = shipperCity.code

      AND             p.tocity = dropCity.code

      AND              p.shipdate >= '$(varStateDate)'

      AND     p.deliverydate <= '$(varEndDate)'

      ;

      There must be a simple way to do this.

       

      2)  Now that I have these records, how do I perform aggregate functions on these columns, similar to this:

       

      SELECT  p.mabcode as mabcode,

                                 shipperCity.state PU_state,

               delCity.state DR_state,

               count(p.pronumuk) as trips,

               max(toh.loadedmiles) as Total_Miles,

                                avg(toh.loadedmiles) as Avg_Miles,  //calc these

               max(ih.totalweight) as Total_Wgt,

                                avg(ih.totalweight) as Avg_Wgt,

               max(ih.totalcharges) as Total_Charges,

                                avg(ih.totalcharges) as Avg_Charges,

               max(ph.paytotal) as Total_Pay,

                                avg(ph.paytotal) as Avg_Pay

                                    FROM

                                    [all the data I've gathered together]

       

       

      I know I'd also have to group by PU_state, DR_state, that's like a Part B to this.

       

      Any help would be very much apprecated.

       

      I have gone through the online Qlikview community and searched the developer's manual, but I cannot seem to find the answer.

       

      My datasoures are Pervasive Databases, but don't worry too much about that.  Just think of them as your standard relational database.