Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
posywang
Creator
Creator

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

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.

1 Solution

Accepted Solutions
Anonymous
Not applicable

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:

Capturar.PNG

View solution in original post

3 Replies
isingh30
Specialist
Specialist

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
Not applicable

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:

Capturar.PNG

posywang
Creator
Creator
Author

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