topic Re: Group by in Load statement, how do I pick values from the same row? in QlikView

Group by in Load statement, how do I pick values from the same row?

cicciput — Mon, 08 Jun 2015 17:39:16 GMT

I have a one-to-many Account - Contact structure, where a single Account can have many Contacts.

What I need is simply to load the Account with first and last name of the first contact — which happens to have the lowest %ContactID — but I don't know, once I pick Min(%ContactID) how do I pick also first and last name of that row.

This does not work:

Only, as intended, returns null when more than one value is found
placing Contact.FirstName and Contact.LastName in Group By returns multiple rows for the same account

LOAD %AccountID,

Account.Name

Account.DCF

FROM Account.qvd (qvd);

LEFT JOIN (Account)

LOAD %AccountID,

MIN(%ContactID) AS %ContactID,

Only(Contact.FirstName) & ' ' & Only(Contact.LastName) AS Account.PrimaryContactName

RESIDENT Contact.qvd (qvd)

GROUP BY %AccountID

ORDER BY %ContactID;

Thanks for your support

P.S.: the only solution I found is another LEFT JOIN (Account) with Contact and load corresponding first and last name, I was trying to understand if the same result can be achieved with one single pass and/or what is more efficient.

Re: Group by in Load statement, how do I pick values from the same row?

Clever_Anjos — Mon, 08 Jun 2015 17:41:38 GMT

Try using MINSTRING() and FirstValue

t:
LOAD * INLINE [
    Account, FirstName, LastName
    1, john, doe
    1, william , smith
    1, abraham, lincoln
];


LOAD
  Account,
  MinString(FirstName) as MinStringFirstName,  //  Abraham
  FirstValue(FirstName) as FirstValueName  // john
Resident t
Group by Account;

Re: Group by in Load statement, how do I pick values from the same row?

sunny_talwar — Mon, 08 Jun 2015 17:44:43 GMT

Try this:

LEFT JOIN (Account)

LOAD %AccountID,

Min(%ContactID) AS %ContactID,

FirstValue (Contact.FirstName & ' ' & Contact.LastName) AS Account.PrimaryContactName

RESIDENT Contact.qvd (qvd)

GROUP BY %AccountID

ORDER BY %AccountID, %ContactID;

Re: Group by in Load statement, how do I pick values from the same row?

swuehl — Mon, 08 Jun 2015 17:46:04 GMT

Try FirstSortedValue:

LOAD %AccountID,

MIN(%ContactID) AS %ContactID,

FirstSortedValue(Contact.FirstName, %ContactID) & ' ' & FirstSortedValue(Contact.LastName, %ContactID) AS Account.PrimaryContactName

RESIDENT Contact.qvd (qvd)

GROUP BY %AccountID

ORDER BY %ContactID;

Since you have sorted your records, also just a FirstValue(Contact.LastName) might work.

Re: Group by in Load statement, how do I pick values from the same row?

cicciput — Mon, 08 Jun 2015 17:48:33 GMT

Won't MINSTRING return the first and last names in alphabetical order, regardless they are of the first or even of the same contact?

Edit: I saw your edit, now I got it, what I need is the first entered, not the first in alphabetical order, thanks anyway

Re: Group by in Load statement, how do I pick values from the same row?

cicciput — Mon, 08 Jun 2015 17:54:52 GMT

Thank you sunindia

Re: Group by in Load statement, how do I pick values from the same row?

cicciput — Mon, 08 Jun 2015 17:55:48 GMT

Thank you for the explanation