topic Re: New to QV needs help with :Order by, firstvalue? in QlikView

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

tomovangel — Wed, 25 Nov 2020 16:16:04 GMT

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

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

sunny_talwar — Mon, 17 Jul 2017 15:21:18 GMT

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?

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

tomovangel — Tue, 18 Jul 2017 06:05:00 GMT

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

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

tomovangel — Tue, 18 Jul 2017 07:19:45 GMT

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

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

tomovangel — Tue, 18 Jul 2017 07:55:21 GMT

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;