EducationSoftwareStrategy.com
StrategyCommunity

Knowledge Base

Product

Community

Knowledge Base

TopicsBrowse ArticlesDeveloper Zone

Product

Download SoftwareProduct DocumentationSecurity Hub

Education

Tutorial VideosSolution GalleryEducation courses

Community

GuidelinesGrandmastersEvents
x_social-icon_white.svglinkedin_social-icon_white.svg
Strategy logoCommunity

© Strategy Inc. All Rights Reserved.

LegalTerms of UsePrivacy Policy
  1. Home
  3. Topics

KB287885: Joining two Excel files during Data Import does not join correctly in MicroStrategy Web 10

Stefan Zepeda

Salesforce Solutions Architect • Strategy

In MicroStrategy Web 10, multiple Excel files can be imported at the same time. When importing Excel files containing the same attributes and metrics, the attributes are automatically linked. But if the metrics have the same name in both Excel files, the data will fail to join correctly

SYMPTOM:
In Strategy Web 10, multiple Excel files can be imported at the same time. When importing Excel files containing the same attributes and metrics, the attributes are automatically linked. But if the metrics have the same name in both Excel files, the data will fail to join correctly.
Consider the example below where the Excel file 1 contains data only for January and the Excel file 2 contains data only for February. The expected behavior would be as shown below. Notice the 2 metrics have not yet been joined.

ka02R000000kcPnQAI_0EM4400000025UH.jpeg

If the metrics are joined by them having the same name, notice in the image below that the data for the Month attribute has joined correctly and will display both months when just the Month attribute is displayed.

ka02R000000kcPnQAI_0EM4400000025UB.jpeg

However when the joined metric is added to the grid, only data for February appears, as shown below.

ka02R000000kcPnQAI_0EM4400000025UC.jpeg

STEPS TO REPRODUCE:

  1. Create an Excel file with the following data.


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)MonthLetterMetricJanuaryA823


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)MonthLetterMetricJanuaryA823


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)MonthLetterMetricJanuaryA823


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)MonthLetterMetricJanuaryA823


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)MonthLetterMetricJanuaryA823


    select a11.CITY_ID CITY_ID,
    max((a11.date + (a11.BALANCE * 0))) WJXBFS1
    into #ZZTJY00L903MD00
    from CITY_DATE a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)
    group by a11.CITY_ID

    select min(a11.WJXBFS1) COMMONDATE
    into #ZZTJY00L903MD01
    from #ZZTJY00L903MD00 a11,
    LU_CITY a12
    where a11.CITY_ID = a12.CITY_ID
    and a12.COUNTRY_ID in (3, 2)

    select distinct a12.CITY_ID CITY_ID,
    a13.date date
    into #ZZTJY00L903MQ02
    from #ZZTJY00L903MD01 a11,
    LU_CITY a12,
    LU_DATE a13
    where (a11.COMMONDATE = a13.date)


    select a11.CITY_ID CITY_ID,
    a13.CITY_DESC CITY_DESC,
    a11.date date,
    a11.BALANCE BALANCE
    from CITY_DATE a11,
    #ZZTJY00L903MQ02 a12,
    LU_CITY a13
    where a11.CITY_ID = a12.CITY_ID and
    a11.date = a12.date and
    a11.CITY_ID = a13.CITY_ID
    and a13.COUNTRY_ID in (3, 2)






























    IntegerDecimal or Numeric with scale 0DecimalNumericDouble, Float, RealDouble, Float, Real (all are mutually compatible with each other)DateTimestampTimeTimestampCharVarChar (but not NChar or NVarChar)NCharNVarChar (but not Char or VarChar)MonthLetterMetricJanuaryA823

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345


  2. Create another Excel file with the following data.

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345

    January
    B234JanuaryC2345MonthLetterMetricFebruaryA823FebruaryB234FebruaryC2345


  3. In Strategy Web 10.2, click Add External Data.
  4. Choose File from disk.
  5. Click Choose files.
  6. Select the two Excel files.
  7. Click Finish.
  8. Save the cube.
  9. Choose Prepare dashboard.
  10. Add Month to Rows dropzone.
  11. Notice that both months appear.
  12. Add a metric to columns dropzone.
  13. Notice only second month appears.
  14. Edit the cube and rename one of the metrics.
  15. Create a VI dashboard off of the new cube and add the month attribute and both metrics to a grid visualization.
  16. Notice that all values now appear as expected.

CAUSE:
This is a limitation of Strategy Web 10. Strategy Web 10 does not yet support combining data from 2 different Excel files. Currently, support is only available between worksheets inside the same Excel file. The limitation is not specific to Visual Insight dashboards.
ACTION:
Currently this issue is still being reviewed for feasibility by our Technology team and it is not scoped for any upcoming scheduled Strategy release.
WORKAROUND:
Before importing the Excel files copy the data from one of the Excel files and paste it into a new worksheet inside of the other Excel file. Proceed with the import of the Excel file with the 2 worksheets. Strategy Web 10 will prompt to select the worksheets to upload, as shown below:

ka02R000000kcPnQAI_0EM4400000025UI.jpeg

 Select both sheets and continue. At this point, Strategy Web 10 will realize that the data is similar and will prompt to confirm the joining, as shown below:

ka02R000000kcPnQAI_0EM4400000025UE.jpeg


Partitioned Tables
The following tables have the same set of objects. Do you want to group these partition tables into a single table?

Select: Yes and the issue will not occur.
 

Comment

0 comments

Details

Knowledge Article

Published:

April 26, 2017

Last Updated:

April 26, 2017