Skip to Content

Merge two rows in one row with extra column


Hello,

We are on BI 4.1 on Windows. I am an administrator and want to write a basic WebI report. How can I merge the master and details rows into one row. The customer no, Name, item comes from TAB1 and code and value comes from TAB2.

Customer no Name Item code value

12345 Name1 IT1 17 23.00

12345 Name1 IT1 63 45.00

Teh TAB2 will always have multiple rows for one item based on the code field. I am only interested in two always. The code 17 means price and 63 means maintanance cost for 5 years.

I want it to display in one rows like -

Customer no Name Item Price MaintCost

12345 Name1 IT1 23 45.00

The value of Price and MaintCost will defined based on the code field, which is in TAB2.

How it is possible in Web Intelligence 4.1? I am hoping it is something simple to model for a technical administrator.

Please let me know.

Best Regards,

SS

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Best Answer
    Posted on Jan 06, 2014 at 05:28 PM

    Hi Surendra,

    As you are saying Code and Values are dependent on Item, pull Item also in TAB2 [ am assuming you

    are referring data provider as TAB] and then merge TAB1 and TAB2 on Item column.

    After merging, create two detail variables for Price and MainCost and associate with Merge dimension Item. Use below formulas for Price and MainCost.

    Price:

    Case when code=17 then [Price] End

    MainCost:

    Case when code=63 then [Maincost] End

    Hope this helps..

    Thanks,

    Bala

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi,

      Create two queries one for Tab1 and another one for Tab2. merge these queries by Customer No only. Make unchecked option of auto-merge dimension in document properties. ie only merged by Customer No.(if Unique Id for both table).

      Then drag the objects Customer No from merged dimension and Name and Item from Table 1 and define the variable for price and Maincost base on Code value in Table2.

      ie) if(Q2.Code=17;[Price]) & if(Q2.Code=63;[Maincost]

      Option 2:

      Define Detail object for Tab2 values associated with merged dimension, then define condition based on these detail objects.

      Thanks,

      Parthi.

  • author's profile photo Former Member
    Former Member
    Posted on Jan 07, 2014 at 06:38 PM

    Hi,

    Create two queries one for Tab1 and another one for Tab2. merge these queries by Customer No only. Make unchecked option of auto-merge dimension in document properties. ie only merged by Customer No.(if Unique Id for both table).

    Then drag the objects Customer No from merged dimension and Name and Item from Table 1 and define the variable for price and Maincost base on Code value in Table2.

    ie) if(Q2.Code=17;[Price]) & if(Q2.Code=63;[Maincost]

    Option 2:

    Define Detail object for Tab2 values associated with merged dimension, then define condition based on these detail objects.

    Thanks,

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 06, 2014 at 05:42 PM

    You can do this at the universe or at the report level.

    At the universe, you would create a price object as:

    CASE WHEN table.code=17 THEN table1.value END

    Create a MaintCost object the same way but swap 17 for 63

    At the report level, you would use If then else logic, with a price variable defined as:

    =If([code]=17;[value])

    Again, swap 17 for 63 to define the MaintCost variable.

    Regards,

    Mark

    Add a comment
    10|10000 characters needed characters exceeded

    • Thank you all for a reply.

      I was able to define two variables as dimention. I see them in the screen and I am able to drag them in Design mode in the report.

      I create a column on right click, insert column to the right. I drag and drp the variable but the as soon it is added it creates a second line, which not I want.

      I want them in one line.

      Sorry for such a basic question.

      Your help will be greately appreciated.
      Thank you,

      SC

  • author's profile photo Former Member
    Former Member
    Posted on Jan 06, 2014 at 05:22 PM

    Hi surendra singh,

    Customer no Name Item Price MaintCost

    12345 Name1 IT1 23 45.00

    How you r getting this price as 23??

    what exactly is ur requirement??

    Thanks

    Mahesh...

    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.