New to QV needs help with :Order by, firstvalue?

[tomovangel]

Hello dear community !
I have the following problem :
I need to group by company

LOAD "CUSTOMER_ID",

    "CUSTOMER_SINCE",

    COMPANY,    ( this field is the key )

    ADDRESS1,

    ADDRESS2,

    ADDRESS3;

SQL SELECT *

FROM ****************.dbo.COMPANYM1;

This table loads my company data, and is linked through the COMPANY field, to another table which gives me information about service requests from companies.

I need to group by COMPANY field, to show the first value in the field.

Thanks in advance,

-Angel

[sunny_talwar]

I need to group by COMPANY field, to show the first value in the field.

First value in which field? You need this in the script?

[tomovangel]

First value in the field COMPANY.

I did this

store COMPANY into COMPANY.qvd;

drop Table COMPANY;

LOAD  COMPANY,

     only(COMPANY_FULL_NAME)as  COMPANY_FULL_NAME

FROM

[COMPANY.qvd]

(qvd)

Group by COMPANY;

And it worked, if i need anything else i will write, thanks Sunny for the quick answer

[tomovangel]

Yes, I need this in script , because there are some records, which aren't made correctly,

I need the first value in the field COMPANY

For example we have the service request IM11000 , linked to 2 companies

City council-LTD

and City council

Its only 1 company, but when somebody from my service department, implemented it he forgot to write -lTD, so this created another company

The problem is that when i use it in reports
for IM11000 i get the 2 companies, i want to show only the first 1
Thanks in advance

-Angel

[tomovangel · Accepted solution]

So i resolved the issue by trial and error, the correct script is

after the table is stored into qvd we enter the following script

LOAD  COMPANY,

     FirstValue(COMPANY_FULL_NAME)as  COMPANY_FULL_NAME

FROM

[COMPANY.qvd]

(qvd)

Group by COMPANY;