Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Creating a new field based on Grouping

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

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

   

CustomerType of AccountNew Field
587OVERDRAFTS Overdrafts Only
589OVERDRAFTS Overdrafts Only
591CHEQUE ACCOUNTSCheque Accounts Only
594CHEQUE ACCOUNTSCheque Accounts Only
595CHEQUE ACCOUNTSCheque Accounts Only
604CHEQUE ACCOUNTSBoth
604OVERDRAFTS Both
605OVERDRAFTS Overdrafts Only
612CHEQUE ACCOUNTSBoth
612OVERDRAFTS Both
617CHEQUE ACCOUNTSBoth
617OVERDRAFTSBoth
620CHEQUE ACCOUNTSBoth
620OVERDRAFTSBoth
1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Re: Creating a new field based on Grouping

Hi,

Attached you can find a solution of your problem.

View solution in original post

6 Replies
Highlighted
Creator III
Creator III

Re: Creating a new field based on Grouping

Hi,

Attached you can find a solution of your problem.

View solution in original post

Highlighted
Partner
Partner

Re: Creating a new field based on Grouping

Hi Jaume

Thank you. 

Kind regards

Nayan

Highlighted
Master III
Master III

Re: Creating a new field based on Grouping

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

Highlighted
Partner
Partner

Re: Creating a new field based on Grouping

Hi Sasidhar

I have done that already.

Kind regards

Nayan

Highlighted
Creator II
Creator II

Re: Creating a new field based on Grouping

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

Highlighted
Partner
Partner

Re: Creating a new field based on Grouping

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;