How to get rid of Projects that don't have value in one field

[posywang]

Hi, I have two data source and I'd like to merge them but some projects are duplicate. I want to keep the row that has value in "Project Approach" column and get rid of the one with no value, only if there's a duplicate. Sample data and file are attached. Can anyone help? Thanks.

[isingh30]

You want solution in UI or at script level?

Temp:

LOAD Project,

     ProjectDate,

     ProjectApproach

FROM

(ooxml, embedded labels, table is Sheet1)where ProjectApproach > 0;

Temp1:

LOAD Project,

     ProjectDate,

     ProjectApproach

FROM

(ooxml, embedded labels, table is Sheet1)where Project = 'P432';

[Anonymous · Accepted solution]

Hello!

Try this:

Data:

Load * Inline

[

Project, ProjectDate, ProjectApproach

P123, 01/01/2018, Agile

P235, 03/03/2018, Non-Agile

P353, 06/03/2018, Agile

P353, 06/03/2018,

P235, 03/03/2018,

P432, 03/05/2018,

];

FinalData:

NoConcatenate

Load FirstValue(Project) As Project,

         FirstValue(ProjectDate) As ProjectDate,

         LastValue(ProjectApproach) As ProjectApproach

Resident Data

Group By Project, ProjectDate

Order By ProjectApproach;

Drop Table Data;

Result:

[posywang]

Thank you for the solution! It worked like a charm!