Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

How to load distinct value?

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.

1 Solution

Accepted Solutions
Highlighted

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;

View solution in original post

11 Replies
Highlighted

May be this

DISTINCT:

LOAD distinct ProjectCode,

    Project,

    Portfolio,

    Program

FROM

DISTINCT.xlsx

(ooxml, embedded labels, table is Sheet1)

Where ProjectCode <> Previous(ProjectCode);

Highlighted
Creator
Creator

what row you want to delete

?

Highlighted

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;

View solution in original post

Highlighted
Champion III
Champion III

May be  try like this not tested

load distinct * inline [

your data

];

Highlighted
Contributor
Contributor

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

Highlighted

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.

Highlighted
Contributor II
Contributor II

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

Creator
Creator

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

Highlighted
Contributor II
Contributor II

Hi Xia,

Please find attached qvw file.

Regards,

Alkis Michael !