Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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.

2 Replies
Not applicable

Re: Using aggregate functions against table created from multiple datasources

Ok, so scratch Question #2!!  I figured it out using a simple table and expressions.  Works wonderfully!

My remaining question is: 

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 

[see SQL above]

laurens
New Contributor III

Re: Using aggregate functions against table created from multiple datasources

Put you're connectstring with variables in an subroutine like: 

SUB dsGPSProd

     ODBC CONNECT32 TO $(v_DBname);

END SUB

Put the variable-setting and SQL-syntaxis in a FOR NEXT or FOR EACH NEXT routine.

Community Browser