Using group by on multiple columns in Qlik Sense Load Script

Sun, 29 Nov 2015 06:54:10 GMT

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?

Re: Using group by on multiple columns in Qlik Sense Load Script

maxgro — Sun, 29 Nov 2015 18:59:05 GMT

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)

topic Using group by on multiple columns in Qlik Sense Load Script in QlikView

Using group by on multiple columns in Qlik Sense Load Script

Re: Using group by on multiple columns in Qlik Sense Load Script