Help required in logic

[hargun]

hi,

i have one query giving me "OPEN" AND CLOSED" acounts.
the first part of query giving "open" Accounts
union all
Second part of query giving "closed"accounts

The query contains the column Account_status determine "OPEN" and "CLH" account and also having other column determine Account_number.

My jobs design

db2 stage.........xfm.................file....
output i required:
if account_status is open and closed for same account number then give only OPEN Account number and if account_status is closed (no open) then closed account number only

i tried by giving the constraint in the transformer stage not getting the result

Code: Select all

If lnk_ACCOUNT_NUMBER.ACCT_STATUS='OPN' OR lnk_ACCOUNT_NUMBER.ACCT_STATUS='CLH' Then lnk_ACCOUNT_NUMBER.ACCT_STATUS='OPN' Else IF lnk_ACCOUNT_NUMBER.ACCT_STATUS='CLH' THEN lnk_ACCOUNT_NUMBER.ACCT_STATUS='CLH' ELSE lnk_ACCOUNT_NUMBER.ACCT_STATUS='OPN'

Please help me on this

[chulett]

A constraint can't solve your problem seeing as how multiple records are involved. How about trying to use an aggregator?

[hargun]

In aggregator I have given acct_number, tins and soa for grouping keys and in aggregation acct_status (determine open/closed)

I have columns acct_status, acct_number, tins and soa
in the source Query and I need acct_number, tins and soa in the output.

Every acct_number has a primary and secondary account_ holder determined by column soa 1 and 2.

After Aggregator if the same acct_number is closed and open then I need open and if the account number is closed only with no open then I need only closed

Do I required constraint after aggregation? If yes, what should I give?
please help on this.

[chulett]

No constraint. There should only be one record per account post-aggregation. Take min or max on your status, whichever is needed to get your desired survivor. Actual examples might help if you still have issues.

[ShaneMuir]

Surely you can just sort your data appropriately and take the first record?

Eg Sort by:
1. Account Number (Asc)
2. Account Status (Desc)

Use a remove duplicates stage with the key Field of Account Number and keep the first record.

This should cater for all outcomes?

[chulett]

That should work as well, I would think. It's all about getting the right one from the pairs you seem to have.