topic Re: Script for merging two fileds. in QlikView

Script for merging two fileds.

sona_sa — Tue, 30 Dec 2014 08:15:49 GMT

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

Re: Script for merging two fileds.

its_anandrjs — Tue, 30 Dec 2014 08:27:16 GMT

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

Re: Script for merging two fileds.

sona_sa — Tue, 30 Dec 2014 08:40:58 GMT

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

Re: Script for merging two fileds.

its_anandrjs — Tue, 30 Dec 2014 08:46:26 GMT

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

Re: Script for merging two fileds.

jagan — Tue, 30 Dec 2014 08:57:59 GMT

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.