Prepare Data - "Convert to number" without duplicating column

[Former Member]

Hey everyone,

I'm new to Lumira.

I have an Excel file containing several columns with numeric data. The number format for these columns in Excel is set to "number" as well.

However, when I import the data into Lumira, the program thinks the data is text rather than numbers.

When I try to mark a column and click "convert to number", it doesn't change the format of the column in question but rather duplicates the column. E.g. it would produce a column called "Sales (2)" which is in a number format, next to the original "Sales" column in the text format.

When I try to delete the original "Sales" column and just keep "Sales (2)" it doesn't work. As soon as I delete the original column, the duplicate column in deleted as well.

Can anybody help? I just want my columns to be in "123" instead of "ABC" format. And not have every column duplicated and end up with 20+ columns.

Thanks a lot in advance!

Alex

[Former Member · Accepted solution]

I have been facing the same problem for a while.  I assumed Lumira Version 1.29 might fix the problem.  This morning, with the new version 1.29- the same problem exists.

When you have multiple columns, the workaround ( Dimensions to numbers to measures) is time consuming and ineffective way to import data

S

[Former Member · Accepted solution]

Thank you.

Yes, it's time consuming. Plus you can never get rid of the original dimensions. So in your case you will always have NOV (ABC), NOV (123), DEC (ABC), DEC (123), JAN (ABC), JAN (123) etc.

You can hide some columns, but the ABC dimensions (NOV (ABC), DEC (ABC) etc.) will always show up in the list of dimensions in "Visualize" mode. No way to delete them. Just makes the list long and confusing.

[mike_howles4 · Accepted solution]

Srin,

Lumira does not seem to like blank cells and your data set also had string values of '#VALUE!' in it (the literal string, not the Excel formula error placeholder).  Upon addressing those two issues, Lumira will profile the data correctly as a Measure without needing the manual convincing after-the-fact.

Hope this also helps Alexandra.

[Former Member · Accepted solution]

Thanks Mike.  This solves the mystery!!

[former_member183750 · Accepted solution]

Just a bit of detail on the "Lumira does not like blank cells".

When connecting to an Excel spread sheet, Lumira looks at the 1st 2000 rows to determine the data type. If a filed is defined as number is excel but a blank field is encountered, Lumira assumes that this field is a text field. Only way to get Lumira to "see" this field as number, is to ensure that all blank cells have a zero in them. This apparently is by design, thus any change to the behavior would be an enhancement that an be added to the SAP Idea Place. Possibly the best way to work this enhancement would be much like Crystal Reports where we have the "Convert to Null" option.

Additional related info:

If the excel field is formatted as "general" type and the 1st 2000 rows contain integers, the field will be interpreted as an integer. Thus if row 2001 has a decimal, the decimal will be dropped.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow me on Twitter (#SAPLumira)

Got Enhancement ideas? Use the SAP Idea Place 

[Former Member · Accepted solution]

Hi Mike, hi Lurek,

thank you very much for your reply. This seems to be the problem for me as well!

I have empty cells in between cells with numbers in the .xls. I could put "0" in all of them; will take a while though, because it's a few hundred cells and they are not all next to each other but spread throughout the sheet. Since I have already converted to numbers & measures in Lumira now, I won't change the source file this time; but will keep in mind for next time.

It's good to know what's happening though & sort of makes sense that Lumira would perceive columns with empty cells as text.

What still doesn't make sense to me is: when I do manually CONVERT these columns to numbers in Lumira, why does it always duplicate the old columns, rather than replacing them?

Right now I have duplicated every ABC column/dimension to 123. And I have 70 ( ! ) dimensions now, instead of the original 30 something. Every dimension is in the list twice, once as text and once as number value. I have now put an "xx_" in front of the text dimensions I wanted to delete but couldn't, so at least they will appear at the very bottom of the list. But it's very annoying.

I know that other tools (such as Tableau) do allow to convert values without duplicating. So I wonder if this "problem" in SAP Lumira is by design as well or if it's just an error?

[mike_howles4 · Accepted solution]

Hi, Alexandra, glad that this helps a little.

It's good to know what's happening though & sort of makes sense that Lumira would perceive columns with empty cells as text.

I'd much rather it be an checkbox option when importing the data like 'Treat blank/empty cells as zeros'.

What still doesn't make sense to me is: when I do manually CONVERT these columns to numbers in Lumira, why does it always duplicate the old columns, rather than replacing them?

I'm not sure if I'm understanding this one completely, but it reminds me of a major annoyance that I have with importing data.  If it is meant to be a Measure, I do *not* want the column to show up as a dimension as well.  It's useless to use it in this way probably 95% of the time.  I've not found any setting to disable this, but the reason it annoys me so much is that without fail, I'll drag in the column to a chart and always erroneously pick the 'dimension' version of it in error.  (Yes, I know Measures are above the Dimensions, but I always look in the Dimension section first, probably a habit picked up from Webi) - If anything, I'd also wish that this was a simple checkbox setting when importing data as well to say 'Create Dimensions for Measures'.  Maybe it's already there and I'm just blind! 

Anyways, that is all from me for now, before I'm told to go to Idea Place hehe.

[Henry_Banks · Accepted solution]

Agreed Mike. Another annoyance of mine, is when i chose to 'hide object' in the prepare room, i'd want them to remain hidden when using object pickers for the chart feeds! ARGH!

[Former Member · Accepted solution]

Maybe I didn't explain it very well. Below a very simple example, with Screenshots. I have an Excel file with three columns of numbers. But when I import them into Lumira, two of them are recognized as text.

So what do I do? I go to the "Prepare" tab, mark the columns I want as numbers and click "Convert to Number". BUT: it doesn't replace the two text (ABC) columns by number (123) columns. It duplicates them, so I end up having 5 columns!

I know, I can hide the columns.

But then I go to the "Visualize" tab to create my graph - and in the dimensions list it shows me 5 dimensions.

Column 1           ABC

Column 1 (2)      123

Column 2           123

Column 3           ABC

Column 3 (2)      123

This is super-annoying. I want the list of dimensions like this (see below).

Column 1           123

Column 2           123

Column 3           123

Just three dimensions, all in the number format.

But this doesn't seem to be possible!?

[harjeetjudge · Accepted solution]

I can explain why a measure is also shows up in the dimension list.  It allows you to create calculated dimension that can later be converted into a measure.

Eg:

Let's say I have a GDP measure.  I can create a new calculated measure using this GDP measure.

New GDP = GDP * 2

If I do this on the dimension I can create a calculated dimension:

New GDP2:  If {Country} = "United States" then {GDP}*2 else {GDP}

I can now convert New GDP2 dimension into a measure.  Notice the difference in the screenshot when I plot these 2 measures against Country dimension.

[TammyPowlas]

Hello Alexandra,

Can you use the camera icon to share how your Excel file looks with sample data? 

[Former Member]

Hi Tammy,

thank you for your reply. The data is somewhat confidential, but here are two screenshots (what it looks like in Lumira and the original columns in the source file) that should help illustrate the problem.

Thanks for your help!

Alex

[TammyPowlas]

Hi Alexandra - thank you for the screen shots - they are helpful

In Excel, could you try the "Paste Special" feature outlined here Excel Data Entry -- Fix Numbers Formatted as Text and then see if Lumira could read those as numbers/measures?

[Former Member]

Thank you, Tammy! I tried this; however it Lumira still sees them as text. So, I don't believe this was the problem. Excel certainly recognizes the values as numbers - I can perform calculations, such as adding them up - only Lumira doesn't.

But isn't there a way to just convert the values to numbers in Lumira? I mean, I already CAN convert them to numbers. Now I just need to find out how to replace the original ABC columns in Lumira by the converted 123 ones, rather than having duplicate columns. Surely that must be possible somehow, no?

Thanks again,

Alex

[TammyPowlas]

Hi Alexandra - you can select the number and right click "Create Measure" as shown below in the Prepare room - see below:

[Former Member]

Hi Tammy,

thanks, I have tried this as well. However, creating measures only works when I have converted the values to numbers first.*

So, the problem remails:

First thing I have to do is convert every column/dimension from text (ABC) to numbers (123). And by doing so, I double the amount of my columns/dimensions, because the original ABC columns are not replaced, for some reason, but duplicated.

Isn't there a way to actually convert, i.e. overwrite ABC columns to get 123 columns?

Alex

*Otherwise it cannot read the values as numbers. I could send another screenshot, but really, it doesn't work. I have to convert the columns/dimension from "ABC" to "123" first. Only then I can create measures and it works.

[TammyPowlas]

I don't know of a way to overwrite; I see your problem but I have no immediate alternative ideas...

[Former Member]

Ok. 😕 Thanks again.

In case anyone else has an idea, here is the problem again, in pictures:

Here's what happens if I want to convert an ABC dimension to a measure (doesn't work, just counts every value as 1).

If I "convert to numbers" first, then turn the dimension into a measure, it works and gives me a proper graph! (see below)

However, then I have all these duplicate columns that I don't want. Too many columns; makes the analysis confusing.

I want to get rid of the original (ABC) columns and just keep the ones where the values are recognized as numbers (123).

Thanks in advance for any advice on how to do this.

Alex

[Former Member]

Small Update: I found out how to hide columns in the "Prepare" section, so I can just hide the columns I don't want, to make it more clear and structured.

However, I still don't know how to hide dimensions. If I'm in the "Prepare" section and click on a dimension, I can "hide from dataset panel". But it doesn't seem to work; the dimension continues to show up under dimensions!

If I cannot delete the original dimensions, maybe I can at least hide them! If you know how, please let me know!

Thanks!!

Alex