Creating a new field based on Grouping

[nayanqlik]

Hi

I have the following table below, Customer and Type of Accounts.  Some customers have either Overdrafts only, some have Cheque Accounts only and others have both.  I want to create a field to reflect this, see second table below  How do I do this in the backend?

Kind regards

Nayan

Customer	Type of Account
587	OVERDRAFTS
589	OVERDRAFTS
591	CHEQUE ACCOUNTS
594	CHEQUE ACCOUNTS
595	CHEQUE ACCOUNTS
604	CHEQUE ACCOUNTS
604	OVERDRAFTS
605	OVERDRAFTS
612	CHEQUE ACCOUNTS
612	OVERDRAFTS
617	CHEQUE ACCOUNTS
617	OVERDRAFTS
620	CHEQUE ACCOUNTS
620	OVERDRAFTS

Customer	Type of Account	New Field
587	OVERDRAFTS	Overdrafts Only
589	OVERDRAFTS	Overdrafts Only
591	CHEQUE ACCOUNTS	Cheque Accounts Only
594	CHEQUE ACCOUNTS	Cheque Accounts Only
595	CHEQUE ACCOUNTS	Cheque Accounts Only
604	CHEQUE ACCOUNTS	Both
604	OVERDRAFTS	Both
605	OVERDRAFTS	Overdrafts Only
612	CHEQUE ACCOUNTS	Both
612	OVERDRAFTS	Both
617	CHEQUE ACCOUNTS	Both
617	OVERDRAFTS	Both
620	CHEQUE ACCOUNTS	Both
620	OVERDRAFTS	Both

[jaumecf23 · Accepted solution]

Hi,

Attached you can find a solution of your problem.

[nayanqlik]

Hi Jaume

Thank you. 

Kind regards

Nayan

[sasiparupudi1]

Please close this thread by marking any helpful and a correct answer

[nayanqlik]

Hi Sasidhar

I have done that already.

Kind regards

Nayan

[sonysree88]

Hi Jaume / Nayan Lalla,

Can you please provide the answer in the steps which function you used.we don't have access to get it download and see the attached qvw file

Regards,

Sonysree

[nayanqlik]

Hi Sonygot

As requested.

Kind regards

Nayan

Table1:

LOAD * INLINE [

    Customer, Type of Account

587, OVERDRAFTS

589, OVERDRAFTS

591, CHEQUE ACCOUNTS

594, CHEQUE ACCOUNTS

595, CHEQUE ACCOUNTS

604, CHEQUE ACCOUNTS

604, OVERDRAFTS

605, OVERDRAFTS

612, CHEQUE ACCOUNTS

612, OVERDRAFTS

617, CHEQUE ACCOUNTS

617, OVERDRAFTS

620, CHEQUE ACCOUNTS

620, OVERDRAFTS

];

Map_Overdrafts:

Mapping LOAD

Customer,

'Y'

Resident Table1

Where [Type of Account]='OVERDRAFTS';

Map_Cheques:

Mapping LOAD

Customer,

'Y'

Resident Table1

Where [Type of Account]='CHEQUE ACCOUNTS';

NoConcatenate

Final_Table:

Load

Customer,

[Type of Account],

if(ApplyMap('Map_Overdrafts',Customer,'N')='Y' and ApplyMap('Map_Cheques',Customer,'N')='Y','Both',

  if(ApplyMap('Map_Overdrafts',Customer,'N')='Y','Overdrafts Only','Cheque Accounts Only')) as [New Field]

Resident Table1;

Drop Table Table1;