topic Re: Inter-record functions in QlikView

Inter-record functions

alisonpwallis — Wed, 04 Feb 2015 14:37:13 GMT

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

Re: Inter-record functions

rubenmarin — Wed, 04 Feb 2015 14:44:51 GMT

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.

Re: Inter-record functions

alexandros17 — Wed, 04 Feb 2015 14:50:09 GMT

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;

Re: Inter-record functions

alisonpwallis — Wed, 04 Feb 2015 15:32:18 GMT

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

Re: Inter-record functions

alexandros17 — Wed, 04 Feb 2015 15:40:03 GMT

Yes Alison,

These other fields must be included in Group by clause

Re: Inter-record functions

alisonpwallis — Wed, 04 Feb 2015 16:26:19 GMT

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