Script for merging two fileds.

[sona_sa]

Hi,

I have 2 fileds, i.e - Format_Data1 and Format_Data2:

Format_Data 1	Format_Data 2
Cost of revenues	COGS
General or administrative 1	Non-Operating Expenses
General or administrative 2	OPERATING EXPENSES
Impairment of Investment	Revenues
Interest and other expense, net 1
Interest and other expense, net 2
Other Income
Research and development
Revenue
Sales and marketing
Tax (expense) benefit

Now I want to merge all the Fileds of Format_Data1 to SEC and Format_Data 2 to MOR and I want one single filed Format that will contains SEC and MOR. When I will click on SEC, All the fields of Format_Data 1 should to select and vice versa for Format _Data 2.

My code is :

If(Format_Data1 = 'Cost of revenues' or Format_Data1 = 'General or administrative 1' or Format_Data1 = 'General or administrative 2' or Format_Data1 = 'Impairment of Investment' or Format_Data1 = 'Interest and other expense, net 1' or Format_Data1 = 'Interest and other expense, net 2' or Format_Data1 = 'Other Income' or Format_Data1 = 'Research and development' or Format_Data1 = 'Revenue' or Format_Data1 = 'Sales and marketing' or Format_Data1 = 'Tax (expense) benefit','SEC',

If(Format_Data2 = 'COGS' or Format_Data2 = 'Non-Operating Expenses' or Format_Data2 = 'OPERATING EXPENSES' or Format_Data2 = 'Revenues','MOR')) as Format

But It is not working properly. So please help me on this.

Thanks in advance.

A Kumar

[its_anandrjs]

Hi,

Here question is are this fields in the same table or in different field, if so then make single field then apply this logic to that field. Please provide any sample file than more easy.

Regards

Anand

[sona_sa]

Both are in the same tables. I tried this one but not working.

[its_anandrjs]

Hi,

You can try this ways this is example you can implement this in your model

Tab1: //Source Table

LOAD

     Format_Data1,

     Format_Data2

FROM

ResData.xlsx

(ooxml, embedded labels, table is Sheet1);

Tab2:

LOAD

Format_Data1,

Format_Data1 as [Common Field]

Resident  Tab1;

Concatenate(Tab2)

LOAD

Format_Data2,

Format_Data2 as [Common Field]

Resident  Tab1;

DROP Table Tab1;

NoConcatenate

New:

LOAD *,

If([Common Field] = 'Cost of revenues' or [Common Field] = 'General or administrative 1' or [Common Field] = 'General or administrative 2' or [Common Field] = 'Impairment of Investment' or [Common Field] = 'Interest and other expense, net 1' or [Common Field] = 'Interest and other expense, net 2' or [Common Field] = 'Other Income' or [Common Field] = 'Research and development' or [Common Field] = 'Revenue' or [Common Field] = 'Sales and marketing' or [Common Field] = 'Tax (expense) benefit','SEC',

If([Common Field] = 'COGS' or [Common Field] = 'Non-Operating Expenses' or [Common Field] = 'OPERATING EXPENSES' or [Common Field] = 'Revenues','MOR')) as Format

Resident Tab2;

DROP Table Tab2;

And you get common field

Regards

Anand

[jagan]

Hi,

Try this

Hi,

Try this script

TableName:

LOAD

*,

If(WildMatch(Format_Data1, 'Cost of revenues' , 'General or administrative 1' , 'General or administrative 2', 'Impairment of Investment', 'Interest and other expense, net 1', 'Interest and other expense, net 2', 'Other Income' , 'Research and development', 'Revenue', 'Sales and marketing', 'Tax (expense) benefit'), 'SEC',

If(WildMatch(Format_Data2, 'COGS', 'Non-Operating Expenses', 'OPERATING EXPENSES' , 'Revenues','MOR'))) as NewField

FROM DataSource;

OR Simply

TableName:

LOAD

*,

If(Len(Trim(Format_Data1)) > 0, 'SEC', 'MOR') AS NewField

FROM DataSource;

Hope this helps you.

Regards,

jagan.