Inter-record functions

[alisonpwallis]

Hi

Please could someone help me with some inter-record functions? I have a list of IDs with a status associated with them. The IDs are sometimes duplicated. Where there is a duplicate I want to select the record on the basis of the status (so discard one record from the pair). I am able to use peek and previous to pick the previous records status for the second row in the duplicate. How do I put the status from the second record into the first?

Example data:

%KEY_APPLICANT          Status

1234                                   U

1234                                   W

1235                                   U

1266                                   C

1267                                   D

1268                                   F

1268                                   W

So I want to be able to select one of the two records with ID (%KEY_APPLICANT) 1234 and one of the two records with ID 1268 based on a hierarchy of statuses : i.e. U comes above W and F comes above W.

Here is my code:

Left Join

Load %KEY_APPLICANT, Count(%KEY_APPLICANT) as Flag

Resident SARAPPD_prepare_4

Group by %KEY_APPLICANT;

Duplicate_check:

Load*,if( [Flag]=1, 'Good','Duplicate') as Indicator

Resident SARAPPD_prepare_4;

store Duplicate_check into SARAPPD_prepare_4.qvd (qvd);

Drop Table SARAPPD_prepare_4;

Drop Table Duplicate_check;

SARAPPD_prepare_5:

Load*,

    if([Indicator]='Duplicate',if([%KEY_APPLICANT]=Previous([%KEY_APPLICANT]), Peek('Status'),'N/A')) as [DuplicateStatus],

From SARAPPD_prepare_4.qvd (QVD);

Thank you for any help

Alison

[rubenmarin]

Hi Alison, maybe adding a RowNo() Field to you Duplicate_check table and read SARAPPD_prepare_4.qvd using Order By RowNoField desc.

This way you read first the 2nd row and using peek or previous can assign the status to the first row.

[alexandros17 · Accepted solution]

If I have understood the question, this is the code you need:

AAA:
LOAD * Inline [
KEY_APPLICANT, Status
1234, U
1234, W
1235, U
1266, C
1267, D
1268, F
1268, W
];

Left Join
LOAD MinString(Status) as StatusToKeep, KEY_APPLICANT Resident AAA Group By KEY_APPLICANT;

BBB:
NoConcatenate
LOAD * Resident AAA Where Status = StatusToKeep;
DROP Table AAA;

[alisonpwallis]

Hi Alessandro

I think this will work but when I try it I get an error message saying :

Aggregation expressions required by GROUP BY clause

Do you know why this might be?

I have more than just these two fields in my table - would that be a reason?

Thanks for your help

Alison

[alexandros17]

Yes Alison,

These other fields must be included in Group by clause

[alisonpwallis]

Hi Alessandro

Thanks for this. I actually turned the minstring part of the code into a max by giving the text values numerical values in order. This then worked perfectly!

Thank you so much for your help

Alison