topic Re: Keeping unique rows in a table in QlikView

Keeping unique rows in a table

Thu, 11 Feb 2016 11:02:57 GMT

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

Re: Keeping unique rows in a table

settu_periasamy — Thu, 11 Feb 2016 11:38:27 GMT

In script, try

Load Distinct [Transaction Reference Number]

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

Re: Keeping unique rows in a table

avinashelite — Thu, 11 Feb 2016 11:42:15 GMT

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

Re: Keeping unique rows in a table

sunny_talwar — Thu, 11 Feb 2016 11:44:23 GMT

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);

Re: Keeping unique rows in a table

Thu, 11 Feb 2016 12:48:26 GMT

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

Re: Keeping unique rows in a table

sunny_talwar — Thu, 11 Feb 2016 14:44:29 GMT

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];

Re: Keeping unique rows in a table

sunny_talwar — Thu, 11 Feb 2016 14:50:38 GMT

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];

Re: Keeping unique rows in a table

swuehl — Thu, 11 Feb 2016 15:05:40 GMT

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;

Re: Keeping unique rows in a table

Tue, 16 Feb 2016 12:51:09 GMT

This code is gives exactly the same result. Thankyou

Re: Keeping unique rows in a table

swuehl — Tue, 16 Feb 2016 22:03:10 GMT

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.

Re: Keeping unique rows in a table

Wed, 17 Feb 2016 11:31:43 GMT

Hi Swuel,

Yes , i am referring to your code and it works as expected by the @abhi_kash .(who started the tread).Thankyou.

@abhi_kash ,Please mark as correct Answer if its works for you.