Combine 2 lines to 1 line with old and new in columns instead of rows

building reports
1

This is my current look :

image
image1926×193 41.1 KB

So I already made field names Old and New price with this logic:
“=[Item credit price] Where ([TimeLine]=“Old”)”
As you see this works fine but now I want to take ‘TimeLine’ out so instead of 4 lines I will have 2
(1 line per SO item)

But #multivalue:

image
image1871×149 37.4 KB

I think because of the ‘Item credit price’ being the same for both lines.
(that column will also go out and I expect that 040 item will also be on 1 line without problems.

Anybody an idea how to fix this?
I always are totally lost when gettting #multivalue…never seem to grasp why I get them… :confused:

2

It is difficult to know for sure without have some sample data to work with. My best guess without more info would be to check “Show rows with empty dimension values” within the Format Table properties.

image
image788×506 19.5 KB

3

I’d do it in a view and include that in the universe. Let the users pull from that pre flattened data.

I’ve seen it called “flattening a table” or sometimes a “pivot”

I had some SQL I’d squirreled away for that task but it was all built around messing with CLOBS and I didn’t keep the table DDL darnit.

I found an example online that comes with

I found that I had to change teh settings on the SQL Fiddle a little to get it to work

After opening that, change the DB type to MySQL, the click the Build Schema button, then you can click the Run SQL button. It works a champ.

4

Thanks nscheaffer,
But it does not change anything.
When I take out field ‘Timeline’ I still get the multivalues.

What more info could I give you?
Don’t think sample data is an option?

5

Sorry kbrazell,

But I am a user not a technician.
I do not have the accesses to change anything to the database.
And to be honest I do not understand the examples you share.

6

I understand and agree that you cannot and should not provide your company data, but sample data is an option. You need to come up with a generic example the exhibits the issue and provide that data in a free-hand SQL query or as raw text so we can put it into a free-hand SQL query (or Excel spreadsheet) to be used as the date source(s).

Do you know how to create a free-hand SQL query and have permission to do so in WebI?

image
image398×476 13.6 KB

If you can get that far, but do not know how to create the SQL query code from your generic sample data this website may help.

The key is we need to know all the details about the data in this report…

  • One query or multiple?
  • If multiple queries, are you merging (and on what)?
  • Data types?
  • Dimension or measures?

If you cannot provide a generic reproducible example that we can work with and try various things it is going to be difficult for us to provide much help.

1 Like
7

Thanks for all the help nscheaffer.
But thats a bridge to far for me.

However I was able to get around with it using a crossable.
Rather still a struggle to get Old and New value clean in the columns but managed that.
So for this problem I’m good.
Thanks.

1 Like