SQL Statement Error

Sun, 29 Nov 2015 23:58:49 GMT

I want to group by all the primary key and select the largest effectivity year per PIN. Here's my example.

PIN OwnerNum Effectivity_Year Total Tax pin123 1003 2014 2,000.00 pin123 1002 2009 3,000.00 pin456 4002 2015 1,500.00 pin456 4001 2014 900.00

And My desired output in sql query

PIN OwnerNum Effectivity Year Total Tax pin123 1003 2014 2,000.00 pin456 4002 2015 1,500.00

I really find this hard because i'm conducting BI report for Real Property Tax System that has no future or past entities for effectivity year.To simplify the data, I created a module whose effectivity year is <=2015. I want to fetch the highest effectivity year only per pin and owner.Thanks for all your help.

Here's my script

[past_or_present effyr]:

LOAD PCINum,

MDINum,

BINum,

SINum,

Parcel,

PType,

OwnerNum,

Date(Max(yr)) as pyear,

PCINum&'-'&MDINum&'-'&BINum&'-'&SINum&'-'&Parcel&'-'&PType as Pin3;

SQL SELECT PCINum,

MDINum,

BINum,

SINum,

Parcel,

PType,

OwnerNum,

FROM PROP.PUB.Property

WHERE yr <= 2015 Group By PCINum,

MDINum,

BINum,

SINum,

Parcel,

PType;

I try to use this query bu t it has some errors.

Re: SQL Statement Error

avinashelite — Mon, 30 Nov 2015 09:48:40 GMT

try like this :

[past_or_present effyr]:

LOAD PCINum,

MDINum,

BINum,

SINum,

Parcel,

PType,

OwnerNum,

yr as pyear,

PCINum&'-'&MDINum&'-'&BINum&'-'&SINum&'-'&Parcel&'-'&PType as Pin3;

SQL SELECT PCINum,

MDINum,

BINum,

SINum,

Parcel,

PType,

OwnerNum,

FROM PROP.PUB.Property

WHERE yr <= 2015 Group By PCINum,

MDINum,

BINum,

SINum,

Parcel,

PType;

Result_table:

LOAD

max(OwnerNum ) as New_OwnerNum ,

PIN as NEW_PIN,

Effectivity_Year as NEW_Effectivity_Year,

[Total Tax] as NEW_Total_Tax

Resident

[past_or_present effyr]

group by

PIN,

Effectivity_Year,

Total Tax;

Resultant table will give you the result

topic Re: SQL Statement Error in QlikView

SQL Statement Error

Re: SQL Statement Error