issue in data foundation

[Former Member]

Hi,

We created multisource universe using Excel and SAP BW cube (Relational connection). We need to join both based on Material. But material key is16 digit string in Cube (Ex: 0000000000001234) and same material value is showing as 1234 in excel. So I think we need to remove leading zeros then join two objects. how we can do it in Data foundation level ? Could any one please help me on this ?? Below is the material definition in cube:

@catalog('Connection')."PUBLIC"."ICUBE"."0MATERIAL"

Thanks,

[former_member184594 · Accepted solution]

In your excel sheet define Material Codes as text and this way you can put the leading zeros in your excel. After that, you try to join your excel sheet with BW table.

[Former Member · Accepted solution]

Hi Zahid,

Thank you for your suggestion...!! But we will receive data in excel files regularly. So keep editing to add leading zeros in excel files requires lot of maintanance. Do we have any function to remove leading zeros in data foundation?? or creating new column in table (in data  foundation) by removing leading zeros then join??? Difinitely there should be a option because all SAP delivered objects length is 16 by default, so it will have leading zeros.

Thanks,

[former_member184594 · Accepted solution]

Ok that's what you do

1. Go to data foundation and right click on the table.

2. Select "Insert Calculated Column"

3. In the prompt screen, write this formula

convert(@catalog('Connection')."PUBLIC"."ICUBE"."0MATERIAL", integer)

4. This formula above should remove the leading zeros from your field. Click ok. You will see the new column at the end of the table.

Be aware that your queries and reports might get slower with this approach if you have large data set.

[Former Member · Accepted solution]

Yes, it is displaying without leading zeros. But one more question here: We are converting it to integer and then joining this integer (Material_New) object with string (Material) object from excel. So will it work as expected??

[former_member184594 · Accepted solution]

Then you have to the same thing for that excel table as well or try to modify your excel as I mentioned earlier.