Skip to Content
0

Remove Duplicate row with null date value on one of the dimension

Dec 19, 2017 at 11:19 PM

40

avatar image

Hi Experts,

I have a requirement to remove the duplicate rows in the webi report. Duplicate in the sense that it has all dimension value duplicated except one date dimension which has null and other has date. I have to retain only the row that has date on it.

Col1 Col2 Col3 Col4 Date

123 456 abc def

123 456 abc def 12/19/17

789 896 ghi jkl

543 983 mno pqr 11/20/17

From above, I have to eliminate the first row which has null date. I created the variable with Previous function on (Col2),(=IF [Col2] = Previous([Col2]) then "0" Else "1")

It returned like this:

Col1 Col2 Col3 Col4 Date Var

123 456 abc def 1

123 456 abc def 12/19/17 0

789 896 ghi jkl 1

543 983 mno pqr 11/20/17 1

But, Ideally the previous should put 0 on the first value. If I filter on 1 then I will loose the row with the Date value and retain the row with Null. Is there any other way that I can retain the row with the date not the null.

Thank you,

Chandan

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Graziano Oliveira de Morais Dec 20, 2017 at 12:10 PM
1

If the row you want to remove always has the null date, you can create a filter in your table to not display when the date is null.

Anyway, you may need to review the ETL process so it does not generate duplicate rows

Share
10 |10000 characters needed characters left characters exceeded
seshu P Jan 03 at 12:05 PM
0
Share
10 |10000 characters needed characters left characters exceeded