Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
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)