Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using group by on multiple columns in Qlik Sense Load Script

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?

1 Reply
maxgro
MVP
MVP

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)