Skip to Content

Webi Report - Merging Rows based on identical column values

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.

webiquestions.jpg (48.8 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

14 Answers

  • Posted on Aug 26 at 04:15 PM

    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")

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 26 at 04:37 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 26 at 04:44 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 26 at 05:00 PM

    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).

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 26 at 05:14 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 26 at 07:35 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 26 at 07:45 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 26 at 07:49 PM

    I get a #ERROR when I try that Mahboob

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 27 at 05:44 PM

    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.


    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 27 at 06:10 PM

    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


    cnull.jpg (26.5 kB)
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.