Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
In script, try
Load Distinct [Transaction Reference Number]
From source where [Report Status ]='N';
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
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);
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
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];
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];
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;
This code is gives exactly the same result. Thankyou
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.