topic Re: Split a Field in to different sub fields in QlikView

Split a Field in to different sub fields

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Split a Field in to different sub fields

swuehl — Sun, 12 Jun 2016 11:25:54 GMT

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

Re: Split a Field in to different sub fields

Sun, 12 Jun 2016 11:52:04 GMT

Thanks swuehl,

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

your valuable time is highly appreciated.

Re: Split a Field in to different sub fields

trdandamudi — Sun, 12 Jun 2016 19:22:10 GMT

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....

Re: Split a Field in to different sub fields

Mon, 13 Jun 2016 11:59:35 GMT

Thanks very Thirumala,

It is more helpful.

Re: Split a Field in to different sub fields

trdandamudi — Mon, 13 Jun 2016 13:31:56 GMT

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

Thanks