Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
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.