Pullling data from multiple datasources [13] in simplified script

Fri, 05 Apr 2013 13:55:18 GMT

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.

Re: Using aggregate functions against table created from multiple datasources

Fri, 05 Apr 2013 18:26:14 GMT

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

My remaining question is:

[see SQL above]

Re: Using aggregate functions against table created from multiple datasources

laurens — Wed, 15 May 2013 15:41:57 GMT

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.

topic Re: Using aggregate functions against table created from multiple datasources in QlikView

Pullling data from multiple datasources [13] in simplified script

Re: Using aggregate functions against table created from multiple datasources

Re: Using aggregate functions against table created from multiple datasources