Webi Report - Merging Rows based on identical column values

[former_member698900]

webiquestions.jpgI'm building a Webi report and would like to consolidate rows based on identical values being present in specified columns. For example, the attached shows 6 rows that I would like to consolidate into 2 rows, so the timestamps in the final two columns are consolidated based on the preceding columns containing identical values.

I've not had much luck using report variables to achieve this, and indeed this may not be a viable option to achieve this anyway.

Any advice or pointers would be appreciated.

[hardolo]

Can't you just break on your first five columns simultaneously (with no headers or footers)?

[former_member698900]

Yes, I have followed that convention exactly and still see the same issue.

[ayman_salem]

Can you write the 3 variables used and also the result table?

and please try to add a comment to the answer, not a new answer.

[former_member698900]

Here is a real-world example of what I am trying to achieve here.

The first column is a system-generated reference number, and the subsequent 3 columns are all formatted as datetime values.

Taking the second column as an example, formula I am using is:

=If(IsNull([Datetime1])) Then (Previous(Self;([RefNum]))) Else [Datetime1]

The output from this is as below:

I do have a number of merged dimensions making up this report, that may or may not be at least some of the cause of the problem.

All the data is presented correctly as per the first screenshot, I just need to align all of the datetimes in a single row for each reference number.

[ayman_salem]

Is the dimension of the Second column "Datetime1"?

To find out which column you are using, can you take a screenshot of the table with the header row (original and the one after entering all the variables you created)?

What about the "Date" column? all other have datetime?

[former_member698900]

Herewith an extract from the report:

And taking the formula for Datetime3N as an example:

All 3 of the DatetimeX columns can be formatted as a datetime, but this option is not available for the DatetimeXN columns.

[ayman_salem]

I really don't understand why #DATASYNC comes into the table after applying the variable.

Does the dimension "Datetime1, 2, 3" come directly from the query or from a calculation?

You talked about the merged dimension earlier. Does the data in the table above come from different queries?

[former_member698900]

All of the datetime values come directly from a query.

Two queries are used here, one to return the correct reference number and the second to return the date timestamp associated with each of the stages (i.e. 1, 2 and 3) in the product's lifecycle. This is what will be the cause of the #DATASYNC I think.

However, the only alternative to this approach would require changes in the Universe which is not a viable option at the moment.

[former_member698900]

That doesn't seem to be working I'm afraid.

Using that formula and taking column 2 as an example, all of the rows are populated with the value 'EFGH', regardless of the value in column 1.

[ayman_salem]

There is a new variable for each column. Notice the bold number in the 3 variables (in name and formula) above.

[ayman_salem]

It's that simple, just make a copy of the variables for each column and change the appropriate column.

Cloumn2N: =If(IsNull([Column 2])) Then (Previous(Self;([Column 1]))) Else [Column 2]

Cloumn3N: =If(IsNull([Column 3])) Then (Previous(Self;([Column 1]))) Else [Column 3]

Cloumn4N: =If(IsNull([Column 4])) Then (Previous(Self;([Column 1]))) Else [Column 4]

....

I hope it helps.

If you are ok with the answer, please accept and close it.

[former_member698900]

Thanks Ayman and apologies for the delay in getting back to you.

The formula works to a degree but I now have a situation where I could have multiple columns (up to 4) presented as per the attached example. As before, the red values are those I am looking to populate with the goal of having a consolidated output with a single line for each column 1 value (i.e. populate all column values for each row and then de-duplicate).

Hope that makes sense.

[ayman_salem]

As I understand your new requirement, here's an idea to do so:

Define the following variable:

Cloumn2N: =If(IsNull([Column2])) Then (Previous(Self;([Column1]))) Else [Column2]

...

hope that helps

[former_member698900]

webiquestions2.jpgI've approached this report from a different angle now, so as to provide an additional column for updating null values.

I'm almost there with this now I think, the attached provides an overview of the remaining step which requires updating the last 4 values in red in column2 (currently NULL) based on the values assigned in the top 2 rows.

Any pointers on the syntax for this appreciated.

[former_member698900]

I get a #ERROR when I try that Mahboob

[ayman_salem]

Is your table a crosstab? If so, which dimensions in columns and rows, which measure also in the body?

Without this information, it is difficult to identify your problem. In addition, there is no need to provide actual data, just dummy data that explains the problem.

[mhmohammed]

Hi Ayman,

Can you please update the DateN as below? This should do it as it'll update the timestamp of each row as 12:00:00 am.

DateN: =ToDate(FormatDate([Date]; "dd.MM.yyyy");"")

Thanks,
Mahboob Mohammed

[former_member698900]

I can't provide any further screenshots due to data protection rules.

All of the populated data is based on dimension values only, and will always conform to the pattern as per my initial attachment.

[former_member698900]

I've just given that a try and I still have the same number of rows as I started with I'm afraid, so no sign of any data grouping. The only difference is that the populated row in each column has changed (i.e. the first column had the first two rows populated, this has now changed to the last two rows).

[ayman_salem]

If you provide more information and a screenshot for the table you are using (type, header, dimension / measure, ...), you may get the right solution for your problem

[mhmohammed]

Hi John,

If you look closely you'll see that the Dates are same, but NOT the timestamps, that's why it's creating multiple rows. So, the formula that Ayman has will remove the timestamps and so, the rows with same Date will group together. Please try it and let us know how it goes.

Thanks,
Mahboob Mohammed

[former_member698900]

I'm not sure how defining a variable like that would help. All of the date timestamps are unique, so all I am really trying to do here is consolidate the rows.

[ayman_salem]

If you provide more information and a screenshot for the table you are using (type, header, dimension / measure, ...), you may get the right solution for your problem.

[ayman_salem]

Define a new variable with just the date without the time and then use the variable in your table

ex. DateN: =FormatDate([Date]; "dd.MM.yyyy")