Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

Excluding Rows

I am trying to find a way to exclude some rows. 

If I have a duplicate in one column, I need to identify a specific name in another column and exclude the remaining rows.

Example 1:

ColumnA          ColumnB

EmailA              ProductA

EmailA              ProductB

EmailA              ProductC

Column A has the same email address but in column B it is for different products.  I am specifically looking for ProductA and would need to exclude the rows for ProductB and ProductC.

Final results:

ColumnA          ColumnB

EmailA              ProductA

However, there will be situations where I have the same email address multiple times where the productA will not exist.  I will need to keep all of those rows.

Example 2:

ColumnA          ColumnB

EmailA              ProductD

EmailA              ProductB

EmailA              ProductC

Final results:

ColumnA          ColumnB

EmailA              ProductD

EmailA              ProductB

EmailA              ProductC

Is this possible to do in QV?

Thanks!

1 Solution

Accepted Solutions
MVP
MVP

Re: Excluding Rows

May be something like this:

Table:

LOAD * Inline [

ColumnA, ColumnB

EmailA,    ProductA

EmailA,    ProductB

EmailA,    ProductC

EmailB,    ProductD

EmailB,    ProductB

EmailB,    ProductC

];

Left Join (Table)

LOAD ColumnA,

  ColumnB as NewColumnB

Resident Table

Where ColumnB = 'ProductA';

FinalTable:

LOAD DISTINCT *;

LOAD ColumnA,

  If(Len(Trim(NewColumnB)) > 0, NewColumnB, ColumnB) as ColumnB

Resident Table;

1 Reply
MVP
MVP

Re: Excluding Rows

May be something like this:

Table:

LOAD * Inline [

ColumnA, ColumnB

EmailA,    ProductA

EmailA,    ProductB

EmailA,    ProductC

EmailB,    ProductD

EmailB,    ProductB

EmailB,    ProductC

];

Left Join (Table)

LOAD ColumnA,

  ColumnB as NewColumnB

Resident Table

Where ColumnB = 'ProductA';

FinalTable:

LOAD DISTINCT *;

LOAD ColumnA,

  If(Len(Trim(NewColumnB)) > 0, NewColumnB, ColumnB) as ColumnB

Resident Table;

Community Browser