Keeping unique rows in a table

[#1]

Table 1:

Transaction reference number	Report status
'18818136SELL	N
'18818136SELL	U

I have a table with number of columns, two of which are 'transaction ref.no.' and 'report status'. There are a number of duplicate trans.ref.no. with stauts U and N but I only want to keep those with status N in the table.

How can I do this? I am new to qlik so don't have much idea.

Regards

[settu_periasamy]

In script, try

Load Distinct [Transaction Reference Number]

From source where [Report Status ]='N';

[avinashelite]

if you want to eliminate the data from the qvw

LOAD *

from table

WHERE [Report Status ]='N';

or if you want to just eliminate in front end

in the expression add the set analysis

sum({<[Report Status ]={'N'}> }sales )

or in the dimension

if ([Report Status ]='N', [Transaction Reference Number]) and check the supress null option

[sunny_talwar]

May be something along these lines

Table:

LOAD [Transaction reference number],

          [Report status],

          OtherFields

FROM Source;

Left Join (Table)

LOAD [Transaction reference number],

          Count(DISTINCT [Report status]) as Count

Resident Table

Group By [Transaction reference number];

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where ([Report status] = 'N' and Count > 1) or ([Report status] = 'Y' and Count = 1);

[#5]

Thanks guys for your response.

I have a further complication in what I am trying to achieve. There is a 2nd condition I have to meet. So I will explain the issue again.

The FinalTable is what my table should look like.

Criteria for FinalTable:

1.If duplicate trans.ref.no with status U and N, then keep U.

2. If duplicate trans. ref. no with multiple U, then keep the one with last Date.

3. But if there are no duplicates then both U and N status to be in the table.

Hope I have explained myself better now.

Regards

[sunny_talwar]

May be this:

Table:

LOAD [Transaction reference number],

          [Report status],

          DATE

FROM Source;

Right Join (Table)

LOAD [Transaction reference number],

          Only([Report status]) as [Report status]

          Date(Max(DATE)) as DATE

Resident Table

Group By [Transaction reference number]

Where [Report status] = 'U';

Right Join (Table)

LOAD [Transaction reference number],

          Only([Report status]) as [Report status],

          Date(Max(DATE)) as DATE

Resident Table

Where [Report status] = 'U'

Group By [Transaction reference number];

[sunny_talwar]

For you sample, find attached the screenshots and application:

Table:

LOAD * Inline [

Transaction reference number, Status, DATE

'18813263SELL, U, 12/01/2014

'18813263SELL, N, 15/09/2015

'18813263SELL, U, 12/01/2016

'18813269SELL, U, 12/01/2014

'18813269SELL, N, 15/09/2015

'18813269SELL, U, 12/01/2016

'18813275SELL, U, 12/01/2014

'18813275SELL, N, 15/09/2015

'18813279SELL, U, 12/01/2014

'18813279SELL, N, 15/09/2015

];

Right Join (Table)

LOAD [Transaction reference number],

          Only(Status) as Status,

          Date(Max(DATE)) as DATE

Resident Table

Where Status = 'U'

Group By [Transaction reference number];

[swuehl]

Or if you don't want to lose a single N record:

SET DATEFORMAT = 'DD/MM/YYYY';

TABLE:
  LOAD [Transaction reference number],
Status as Status,
Date(Max(DATE)) as DATE
Group By [Transaction reference number], Status;
  LOAD * Inline [
  Transaction reference number, Status, DATE
  '18813263SELL, U, 12/01/2014
  '18813263SELL, N, 15/09/2015
  '18813263SELL, U, 12/01/2016
  '18813269SELL, U, 12/01/2014
  '18813269SELL, N, 15/09/2015
  '18813269SELL, U, 12/01/2016
  '18813275SELL, U, 12/01/2014
  '18813275SELL, N, 15/09/2015
  '18813279SELL, U, 12/01/2014
  '18813279SELL, N, 15/09/2015
'18813288SELL, N, 15/09/2015
];

RESULT:
NoConcatenate
LOAD  [Transaction reference number],
FirstValue(Status) as Status,
FirstValue(DATE) as DATE
Resident TABLE
GROUP BY [Transaction reference number]
ORDER BY [Transaction reference number], Status desc;

DROP TABLE TABLE;

[#9]

This code is gives exactly the same result. Thankyou

[swuehl]

Palanisamy Devaraj,

since you've responded to my post, I assume you are referring to my code.

My code is giving the same result as the code posted by whom?

Maybe I missed something, but I don't think that the other suggestions are producing the exact same results.

Note that I've added another test case for criteria 3.