topic Re: Intersect field values in QlikView

Intersect field values

Thu, 27 Mar 2014 13:11:40 GMT

Hi,

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

For example,

Field1

Field2

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?

Re: Intersect field values

nizamsha — Thu, 27 Mar 2014 13:26:04 GMT

use apply map u will get what u want

Re: Intersect field values

mangalsk — Thu, 27 Mar 2014 13:27:28 GMT

Hello,

if(Field1=Field2,Sum(Field3))

Re: Intersect field values

nizamsha — Thu, 27 Mar 2014 13:27:52 GMT

T1:

LOAD * Inline [

Field1

];

T2:

LOAD * Inline [

Field2

] ;

T3:

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

T4:

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

Re: Intersect field values

Thu, 27 Mar 2014 13:40:26 GMT

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.

Re: Intersect field values

mangalsk — Fri, 28 Mar 2014 06:30:50 GMT

Hello,

PFA.

It works properly

Regards,

Re: Intersect field values

muniyandi — Fri, 28 Mar 2014 06:36:18 GMT

hi,

use concat() function.

ex: concat(Days,',')

thanks,

Muni.

Re: Re: Intersect field values

muniyandi — Fri, 28 Mar 2014 06:46:46 GMT

Hi Karthi,

Kindly verify my attachment file.

Thanks,

Muni

Re: Intersect field values

Fri, 28 Mar 2014 09:38:31 GMT

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;

Re: Intersect field values

Sat, 29 Mar 2014 09:47:23 GMT

Hi All,

Thanks for everyone response,

I resolved the problem by simple method mentioned below,

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;