How to load distinct value?

[posywang]

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.

[sunny_talwar]

May be this

DISTINCT:

LOAD distinct ProjectCode,

    Project,

    Portfolio,

    Program

FROM

DISTINCT.xlsx

(ooxml, embedded labels, table is Sheet1)

Where ProjectCode <> Previous(ProjectCode);

[Anonymous]

what row you want to delete

?

[sunny_talwar · Accepted solution]

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;

[Chanty4u]

May be  try like this not tested

load distinct * inline [

your data

];

[Anonymous]

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

[sunny_talwar]

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.

[Anonymous]

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

[posywang]

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

[Anonymous]

Hi Xia,

Please find attached qvw file.

Regards,

Alkis Michael !