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

[cicciput]

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.

[Clever_Anjos]

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;

[sunny_talwar · Accepted solution]

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;

[swuehl]

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.

[cicciput]

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

[cicciput]

Thank you sunindia

[cicciput]

Thank you for the explanation