Compare columns in pivot table and show rows

[Mukesh_s]

Hi, I am trying to compare the columns in a pivot table and show rows only if their is a difference between column values.

Below is the example pivot  

As their is a difference between the columns for the members 1016 and 1019 which are highlighted in the above image the output must show only these two rows in the pivot table.

Thanks

[E_Røse]

I am thinking something along the line of: aggregating the Only function over the MemberNumber and check if  it returns a value or not. I will see if can come up with an expression that works. It is not straightforward..

See if this works:

if(isnull(only(total<MemberNumber> Coalesce(Value, 'null'))), Only(Value))

So this is the explanation:

only(total<MemberNumber> Coalesce(Value, 'null'))

returns a value  if and  only if the row in your table contains exactly one unique value(since null is converted to 'null', null counts as a value). So if this returns zero, then we show the values in the row.  under Add-ons, un-chek the include zero-values check box, to remove rows with only null values.

Please like and mark my answer as a solution, if it resolved your issue.

[Mukesh_s]

Hi Elin, 
thanks for the reply. I am not trying to get rows with the null values, I want to get the rows if their is a difference between the data with respect to their dates

Ex: I don't want rows with  - A     A 

I want rows which have   - A     B  or C or D etc. which doesn't match with first column

[E_Røse]

@Mukesh_s 

My expression returns the value B in the row for 1016 and 1019. That is what you asked for isn't it?

However, I said that to remove the rows with all zeroes, un-check 'include null values for the MemberNumber dimension. That was wrong. Instead, under Add-ons, un-chek the include zero-values check box.  I will correct my previous post.

If you want to be sure both columns are shown, you could do like this instead.

if(isnull(only(total<MemberNumber> Coalesce(Value, 'null'))), coalesce(Only(Value), 'missing'))

Then you will have this:

Please like and mark my answer as a solution, if it resolved your issue.

[Mukesh_s]

I think the previous data is confusing, so see the below data table

ID	6-Mar	7-Mar
1	A	A
2	A	B
3	B	B
4	D	D
5	B	C

 I want the below table as the IDs  2 and 5 has different values

ID	6-Mar	7-Mar
2	A	B
5	B	C

[E_Røse]

Can you try my expression, and if it is wrong, can you give me an example of what it is doing wrong!

Because in my app, the expressions I suggested still gives exactly what you say that you want..

I can see one problem though, and that is that if the values for 1016 and 1019 are missing, and not null, then those two roes will not show. I am not sure what is the best way to fix that. A possible solution is adding records in the load script for membeNumber and L_FRP with value =null()  for combinations of memberNumber and L_FRP that does not exist in your dataset.

[E_Røse]

Regarding the last comment in my previous post, you could add this code in the load script

noconcatenate
tmp:
Load distinct MemberNumber 
Resident Data;

outer join(tmp)
Load distinct L_FRP Resident Data;

Outer Join(Data)
Load * resident tmp;
Drop table tmp;

 (replace 'Data' with the name of your table. ) 

Please like and mark my answer as a solution, if it resolved your issue.

[Mukesh_s]

Elin, 

I am not dealing with the missing values or null values. I want the rows which has different data, see my example data in the comment above.

[henrikalmen]

Maybe what you need is something like a calculated dimension "=if(column(1)=column(2), MemberNumber, null())", but the column() function isn't valid for pivot tables. Right now I'm not sure how to acheive that, but do you think that this is the concept that you need? Have I understood your question correctly now?

[Mukesh_s]

Hi Henrikalmen,

Yes, that is the exact solution that I am looking for but the data is in pivot table. Thanks for your time.