topic Re: How to compare 2 rows of same column using SET ANALYSIS ? in QlikView

How to compare 2 rows of same column using SET ANALYSIS ?

Tue, 21 Mar 2017 06:45:33 GMT

Hi,

I am actually trying to see how I can compare 2 different rows of same column in a same table using SET ANALYSIS.

Table1:

Id Name PropId

1 JOHN 1000

2 JAKE 1000

3 MIKE 10011

4 AMY 10011

I need to check if 'Propid' column having same value then display 'Name'

for example

=If( (If(Id=1 , PropId)) = (If(Id=2 , PropId)) , Name)

but it is not working. Please Help!

Thanks in Advance

Re: How to compare 2 rows of same column using SET ANALYSIS ?

Anil_Babu_Samineni — Tue, 21 Mar 2017 06:51:19 GMT

I need to check if 'Propid' column having same value then display 'Name'

If(PropId = Id, Name)

=If( (If(Id=1 , PropId)) = (If(Id=2 , PropId)) , Name)

From this, What are you trying? May be?

If(Id=1, If(Id=2, PropId, Name))

If(Id=1, ProdId, If(Id=2, ProdId, Name))

If(Id=1 and Id=2, ProdId, Name)

Re: How to compare 2 rows of same column using SET ANALYSIS ?

neha_shirsath — Tue, 21 Mar 2017 07:28:41 GMT

Hi Sheela,

Can you tell me what will be output in this case?

Means now in given case for id1& id2 propid is 1000 then you want to display Name-JOHN and JAKE?

And if its not equal to then??

like suppose i have below table-

Table1:

LOAD * Inline [

Id , Name, PropId

1 , JOHN , 1000

2 , JAKE , 1000

1 , ABC, 1001

3 , MIKE , 10011

4 , AMY , 10011

];

So what will be the output for ABC name?

Thanks,

Neha

Re: How to compare 2 rows of same column using SET ANALYSIS ?

Tue, 21 Mar 2017 08:35:28 GMT

Hi Anil,

Thanks for reply.

For Example:

I will pass Id as 1.

PropId is 1000 for Id 1.

If PropId Column contains value 1000, display all those related name.

Now If i pass id as 1, i need to display names JOHN and JAKE.

Now If i pass id as 3, i need to display names MIKE and AMY.

Re: How to compare 2 rows of same column using SET ANALYSIS ?

Tue, 21 Mar 2017 08:38:09 GMT

Hi neha,

Table1:

LOAD * Inline [

Id , Name, PropId

1 , JOHN , 1000

2 , JAKE , 1000

1 , ABC, 1001

3 , MIKE , 10011

4 , AMY , 10011

];

So what will be the output for ABC name?

Just display ABC.

Now If i pass id as 1, i need to display names JOHN and JAKE.

Now If i pass id as 3, i need to display names MIKE and AMY.

Thanks

Sheela

Re: How to compare 2 rows of same column using SET ANALYSIS ?

swuehl — Tue, 21 Mar 2017 08:48:10 GMT

You can use the p() function to retrieve related PropId for selected Id, if you then clear selection in Id, you can get the record set with all records with related PropId to selected Id:

=Concat({<PropId = p(PropId), Id= >} DISTINCT Name,', ')

Re: How to compare 2 rows of same column using SET ANALYSIS ?

neha_shirsath — Tue, 21 Mar 2017 09:05:32 GMT

So, your expected output look like-

Id	PropId	Name
1	1000	JOHN
1	1001	ABC
1	1000	JAKE
3	10011	MIKE
3	10011	AMY

Correct me if i'm wrong.

I'm just understanding exactly what your expected output.

Thanks,

Neha

Re: How to compare 2 rows of same column using SET ANALYSIS ?

Tue, 21 Mar 2017 10:30:45 GMT

Hi Neha,

No. I have a straight table. Need to display top 5 members. I am passing Id while loading.

Say am passing Id as 1(Id=1). PropId is 1000 for Id 1. I want to display top 5 names whose Propid is 1000

Thanks

Sheela

Re: How to compare 2 rows of same column using SET ANALYSIS ?

Anil_Babu_Samineni — Tue, 21 Mar 2017 10:35:02 GMT

Look Dimension Limits on your object properties. Or use Rank to get top 5 ranks with may be wildcard search. Can you send the formule which you used. May be better if you would provide sample

Re: How to compare 2 rows of same column using SET ANALYSIS ?

neha_shirsath — Tue, 21 Mar 2017 10:56:12 GMT

Hi Sheela,

So basically you want top n members for whom your for eg. id=1 & propid=1000.

See the attached file,hope it will help you.

Thanks,

Neha

Re: How to compare 2 rows of same column using SET ANALYSIS ?

Tue, 21 Mar 2017 12:31:52 GMT

Hi Sheela,

You can use swuehl 's code for getting your result.

=Concat({1<PropId=p(PropId)>}DISTINCT Name,', ')

and for top 5 results set max number to 5 in presentation tab.

Regards,

Anurag

Re: How to compare 2 rows of same column using SET ANALYSIS ?

Tue, 21 Mar 2017 12:42:15 GMT

Hi Neha,

I am not using licence version of qlikview. Please could you share code if possible?

Thanks

Sheela

Re: How to compare 2 rows of same column using SET ANALYSIS ?

Tue, 21 Mar 2017 14:24:52 GMT

As long as there is direct relation between ID and Name we cant not achieve this.

We can use Set analysis only for Aggregations. Not possible in this case.

Try to create two separate tables and those Name and ID should be related through ProdID.

Name:

LOAD

Name,

ProdId

FROM [lib://Test/Test.xls]

(biff, embedded labels, table is Sheet2$);

ProdID:

LOAD

ID,

ProdId

FROM [lib://Test/Test.xls]

(biff, embedded labels, table is Sheet2$);

Re: How to compare 2 rows of same column using SET ANALYSIS ?

Thu, 23 Mar 2017 04:04:51 GMT

Hi Anurag,

Thanks for reply!

It will work. But my requirement is different. I want to check equality condition like

If(Id=1, [propid]) = If(Id=2,[propid])

If it is true display name.But it is not working , i think syntax is not correct.

Please Help!

Thanks

Sheela

Re: How to compare 2 rows of same column using SET ANALYSIS ?

Anil_Babu_Samineni — Thu, 23 Mar 2017 04:08:58 GMT

in fact, Syntax should this

If(Id=1, [propid], If(Id=2,[propid])

If(Match(Id,'1') = Match(Id,'2'), [prodid])

Or else you may provide exact output you want to see

Re: How to compare 2 rows of same column using SET ANALYSIS ?

Thu, 23 Mar 2017 05:07:21 GMT

Hi Anil,

Thank you so much for reply!

I want to check for equality condition like - If Id 1's propid equal to Id 2's propid then display 'Name'.

If ( If(Id=1,[propid] ) == If(Id=2,[propid]) ,Name )

Please help!

Re: How to compare 2 rows of same column using SET ANALYSIS ?

Anil_Babu_Samineni — Thu, 23 Mar 2017 05:59:13 GMT

i am sure, This is disgusting. But you can try or provide sample data

If(Only({<Id = {'1'}>}propid) = Only({<Id = {'2'}>}propid), Name)

Re: How to compare 2 rows of same column using SET ANALYSIS ?

Thu, 23 Mar 2017 06:10:58 GMT

Hi Anil,

Yes! My requirement matches the expression but it is not working:(

Thanks

Sheela

Re: How to compare 2 rows of same column using SET ANALYSIS ?

Anil_Babu_Samineni — Thu, 23 Mar 2017 06:51:18 GMT

I would suggest you to create dummy and share in excel so that we can work on it. I am sorry if you are not able to share the data set. This case, May be other can help you

Re: How to compare 2 rows of same column using SET ANALYSIS ?

Thu, 23 Mar 2017 07:17:27 GMT

Hi Anil,

Sample data.

Id	Prop ID	Name
1	3211	singnaklong
2	3211	donlaya
3	3211	tiwari
4	3545	rahul
5	3545	mohammed
6	3545	najma
7	3559	Nina
8	3559	benny
9	3559	zhou
10	3559	rex
11	3559	stefi

Thanks

Sheela