Intersect field values

[#1]

Hi,

I need to display only the values which are common in two different fields.

For example,

Field1

1

2

3

Field2

1

2

3

4

for the above two fields i need to display the data those are common i.e 1,2,3

Could anyone suggest the solution please?

[nizamsha]

use apply map u will get what u want

[mangalsk]

Hello,

if(Field1=Field2,Sum(Field3))

[nizamsha]

T1:

LOAD * Inline [

Field1

1

2

3

];

T2:

LOAD * Inline [

Field2

1

2

3

4

] ;

T3:

Mapping LOAD Field1 as Field3,RecNo() as Record  Resident T1;

T4:

LOAD   ApplyMap('T3',Field2,'')  as Field4 Resident T2;

[#5]

The fields are from same table.

Actually the original table is,

Metric, Days

AB,1

AB,2

AB,3

AB,4

CD,1

CD,2

CD,3

Field1= if(Metric='AB',Days) Output: 1,2,3,4

Field2= if(Metric='CD',Days) Output: 1,2,3

In above scenario, i need to take only common values from Field1 and Field2.

[mangalsk]

Hello,

PFA.

It works properly

Regards,

[muniyandi]

hi,

use concat() function.

ex:  concat(Days,',')

thanks,

Muni.

[muniyandi]

Hi Karthi,

Kindly verify my attachment file.

Thanks,

Muni

[#9]

Hi,,

Try this

Test1:

Load * Inline [
Metric, Days

AB,1

AB,2

AB,3

AB,4

CD,1

CD,2

CD,3
];

Res1:
Load
SubField(Metric&'-'&Days,'-',1) as Key1,
if(Metric='AB',Days) as Field
Resident Test1
Where Metric <> 'CD';

Right Join
Res2:
Load
SubField(Metric&'-'&Days,'-',1) as Key2,
if(Metric='CD',Days) as Field
Resident Test1
Where Metric <> 'AB';

Drop Table Test1;

Res2:
CrossTable(KeyType,Metric,1)
Load Field,
Key1,
Key2
Resident Res1;

Drop Table Res1;

[#10 · Accepted solution]

Hi All,

Thanks for everyone response,

I resolved the problem by simple method mentioned below,

A:

Metric, Days

AB,1

AB,2

AB,3

AB,4

CD,1

CD,2

CD,3

Load if(Metric='AB',Days)     as field1

Resident A;

Load *,

field2 as common

where exists(field1,field2);

Load if(Metric='CD',Days) as field2

Resident A;