topic Re: How to load distinct value? in QlikView

How to load distinct value?

posywang — Thu, 06 Jul 2017 13:58:24 GMT

Hi,

I'd like to load only one row if it has same Project Code. That one row can be randomly picked from multiple rows. Is this something that can be easily done? Thanks. Sample QVW and source file are attached.

Re: How to load distinct value?

sunny_talwar — Thu, 06 Jul 2017 14:06:45 GMT

May be this

DISTINCT:

LOAD distinct ProjectCode,

Project,

Portfolio,

Program

FROM

DISTINCT.xlsx

(ooxml, embedded labels, table is Sheet1)

Where ProjectCode <> Previous(ProjectCode);

Re: How to load distinct value?

Anonymous — Thu, 06 Jul 2017 14:07:57 GMT

what row you want to delete

Re: How to load distinct value?

sunny_talwar — Thu, 06 Jul 2017 14:08:12 GMT

This one is slightly better

DISTINCT:

LOAD distinct ProjectCode,

Project,

Portfolio,

Program

FROM

DISTINCT.xlsx

(ooxml, embedded labels, table is Sheet1);

Final_DISTINCT:

NoConcatenate

LOAD *

Resident DISTINCT

Where ProjectCode <> Previous(ProjectCode)

Order By ProjectCode;

DROP Table DISTINCT;

Re: How to load distinct value?

Chanty4u — Thu, 06 Jul 2017 14:09:43 GMT

May be try like this not tested

load distinct * inline [

your data

];

Re: How to load distinct value?

Anonymous — Thu, 06 Jul 2017 14:33:38 GMT

If you don't care about which of the other values you get, maybe can you do something like:

DISTINCT:

LOAD distinct ProjectCode,

FirstSortedValue(Project),

FirstSortedValue(Portfolio),

FirstSortedValue(Program)

FROM

DISTINCT.xlsx

(ooxml, embedded labels, table is Sheet1)

GROUP BY ProjectCode

ORDER BY ProjectCode

Re: How to load distinct value?

sunny_talwar — Thu, 06 Jul 2017 14:36:33 GMT

From doesn't allow for Order By Statement, beside you don't even need Order By here because you are using Group by and FirstSortedValue.

But having said that, I would go with Where ProjectCode <> Previous(ProjectCode) because Group by will give slower performance on a bigger dataset compared to Previous. What might help is if the data is already sorted by ProjectCode and then we won't even need a resident load and my first solution will work.

Re: How to load distinct value?

Anonymous — Thu, 06 Jul 2017 14:45:40 GMT

Hi Xia,

I have attached one qvw file. Please have look into it.

Hope this is what you are looking for.

If this answer helps, then mark it correct so that we can close this call.

Thanks

Regards,

Alkis Michael

Re: How to load distinct value?

posywang — Thu, 06 Jul 2017 14:58:54 GMT

Hi I am not able to open a qvf file since I only have Qliview.

Re: How to load distinct value?

Anonymous — Thu, 06 Jul 2017 15:04:41 GMT

Hi Xia,

Please find attached qvw file.

Regards,

Alkis Michael !

Re: How to load distinct value?

Steveo250k — Tue, 30 Apr 2019 12:28:22 GMT

Wow, while all responses are much appreciated, using a keyword for both a file name and a table name makes this example very hard to follow. In effect you are saying load distinct into distinct from distinct. Wow.

Re: How to load distinct value?

Steveo250k — Tue, 30 Apr 2019 12:30:18 GMT

While all responses are much appreciated, using a keyword for both a file name and a table name makes this example very hard to follow. In effect you are saying load distinct into distinct from distinct.