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.
