Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have to find the Max Effectivity Year
per PIN(PCINUM,MDINum,BINum,SINum,Parcel,PType)
. Then join the result with my table on PINPIN(PCINUM,MDINum,BINum,SINum,Parcel,PType)
and Effectivity Year
to get the result.
LOAD
PCINum,
MDINum,
BINum,
SINum,
Parcel,
PType,
OwnerNum,
yr;
SQL SELECT PCINum,
MDINum,
BINum,
SINum,
Parcel,
PType,
OwnerNum,
yr from PROP.PUB.Property INNER JOIN
(SQL SELECT PCINum,
MDINum,
BINum,
SINum,
Parcel,
PType,
max(yr) as max_yr from PROP.PUB.Property WHERE yr <= 2015 GROUP BY SQL SELECT PCINum,
MDINum,
BINum,
SINum,
Parcel,
PType) Property2 ON Property.PCINum,
Property.MDINum,
Property.BINum,
Property.SINum,
Property.Parcel,
Property.PType = Property2.PCINum,
Property2.MDINum,
Property2.BINum,
Property2.SINum,
Property2.Parcel,
Property2.PType AND Property.yr = Property2.max_yr;
Is this possible in qlik sense?
an example, if I understand the question
TABLE:
load
Dim1, Dim2, Dim3, // replace with your join field (PCINUM,MDINum,BINum,SINum,Parcel,PType)
Dim4, Dim5,
[Effectivity Year],
Exp1, Exp2;
sql select .......;
left join (TABLE)
load
Dim1, Dim2, Dim3, // replace with your join field (PCINUM,MDINum,BINum,SINum,Parcel,PType)
max([Effectivity Year]) as MaxYear
resident
TABLE
group by
Dim1, Dim2, Dim3; // replace with your join field (PCINUM,MDINum,BINum,SINum,Parcel,PType)