Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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
Contributor III
Contributor III

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.