Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Both are in the same tables. I tried this one but not working.
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
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.