Split a Field in to different sub fields

[#1]

Hi,

I am very new to Qlik.

I have a field called AccountingID consisting of thousands of rows with different account ID such as (Sales, COGS, other income and expenses etc) and I want to split these field in to subfield as each type of income expenses;

Ex;

AccountingID
500000
500000
600000
700001
700002
800009
950000
140000

I want to write a script as follows;

if Accounting ID 500000 equal to sales, 600000 equal to COGS, 700001 Salary and etc.

Can any body help me to solve this issue.

[swuehl]

Maybe like

LOAD AccountingID,

          Pick(Match(AccountingID, '500000','600000','700000'),'Sales','COGS','Salary') as AccountGroup,

          ...

FROM ...;

Or if you want to check on the start of the ID, e.g. the first two digits:

LOAD AccountingID,

          Pick(WildMatch(AccountingID, '50*','60*','70*'),'Sales','COGS','Salary') as AccountGroup,

          ...

FROM ...;

You can also look into a MAPPING table approach:

Data Cleansing

Mapping … and not the geographical kind

[#3]

Thanks swuehl,

I will try this script and i will back to you,

your valuable time is highly appreciated.

[trdandamudi]

Here is one more way:

Account_Mapping:

Mapping Load * Inline [

Expense_Account,Description

500000,Sales

600000,COGS

700000,Salary

];

Data:

Load *,

ApplyMap('Account_Mapping',Expense_Account,'No Mapping') as Description Inline [

Name,Expense_Account,Amount

A,600000,1000

B,700000,2000

C,500000,3000

];

Hope this helps....

[#5]

Thanks very Thirumala,

It is more helpful.

[trdandamudi]

If you think you got the answer, can you please close the thread by marking the answer accordingly.

Thanks