Forum Discussion

Tobias's avatar
Tobias
New Contributor III
4 years ago

Import TextValue from Matrix-Excel

Hi,

I have an Excel template file to import via the stage. This Excel is using a matrix-style for the values (time in columns) and also one column with a text commentary.

As I have to change the view to “Annotation” in order to import a text value, I was thinking about duplicating the rows by a derivative rule and change the view in that rule. The outcome shows the duplicated rows, but these are missing the text value. So this approch is not working.

I also tried to duplicate one time column and use a parser rule to change the view. Doing so, I would need to identify, which time period I am in during pasing, as otherwise all rows are changed.

Any ideas about that?

Regards,
Tobias

Import
  • NicolasArgente's avatar
    NicolasArgente
    Valued Contributor
    4 years ago

    Please use the TV#:[Annotation] in the Excel and leave the View as YTD.
    Remember that Annotation is not loaded in the cube.
    Look at the help below

    image

     

    image
    • Tobias's avatar
      Tobias
      New Contributor III
      4 years ago

      Thanks for your reply. I was reading the guide multiple times. It seems that I was always skipping that sentence 

    • adykes's avatar
      adykes
      New Contributor III
      2 years ago

      Thank you Nicolas, this response greatly helped me figure out a solution to my issue. I want to include two additional steps / reminders that I had to take in order for my imported Annotation data to show up in a cube view.

      1. Make sure the Text Value field is enabled in your cube's integration settings so the data will be imported to the stage (as Nicolas is showing in his screenshot above) - kudos to this post for that tip

      2. Make sure your cube view Origin member for the annotation data is O#Import - I had to change mine from what I usually use, O#BeforeAdj, because text data does not roll up from O#Import or O#Forms into O#BeforeAdj.

  • TomABC's avatar
    TomABC
    Contributor II
    4 years ago

    And for the thread note there are sample templates in Marketplace for download as well so you can examine sample token formats for cell text, etc.

  • SxD's avatar
    SxD
    New Contributor III
    3 years ago

    Sorry to reopen this thread, but I have a similar question on a Matrix Excel Template load. Can you load an Excel Template file to the Import staging table with multiple columns as the time period with the amounts in each of them? I'm running into an error where it's looking for an Amount and Time column separately. Perhaps I don't have my Data Source setup properly? This is what my template looks like for the amounts:

    If I need to start a new thread, please let me know.

    Thanks,

    Steven

    • NicolasArgente's avatar
      NicolasArgente
      Valued Contributor
      3 years ago

      Hi Steven, 
      Please can you check the title of the columns. 
      I would say, for testing purposes, please try to simplify this naming. (ie. Try only with this format for now : T#[2021M1])  Then do not forget that you need to map all the members in Transformation too. (ie 2021M1 to 2021M1). That should work... and only then you compexify.

      PS: Thanks with a kudos point 🙂

      • SxD's avatar
        SxD
        New Contributor III
        3 years ago

        Hi Nicolas,

        If I make the value just T#[2021M1] then I get an error saying it's missing an amount column. 

        Steven

    • Tobias's avatar
      Tobias
      New Contributor III
      3 years ago

      Hi Steven,
      Please check your datasource settings. The "data structure type" needs to be "Matrix data".

      Regards,
      Tobias

      • SxD's avatar
        SxD
        New Contributor III
        3 years ago

        Hi Tobias - I already have that set to "Matrix data", but still no luck. 

  • Sergey's avatar
    Sergey
    Contributor III
    3 years ago

    Hi Steven !

    In the standard demo, you can find such configuration with the "HoustonBudget" source file, I hope it will help you:

     

    Regards,

    • SxD's avatar
      SxD
      New Contributor III
      3 years ago

      Hi Sergey - I did check the Golfstream app for ideas first and did use as a baseline. This example uses a flat file vs. an Excel (xls/xlsx) file correct? So for the Time periods, I can see they're setting it to a certain length based on the month. However, in the Excel template I'm trying to load, I can't do that because when you upload the Excel file to preview, it's all encoded. 

      Happy to send an email to Partner Enablement if it's easier to have someone jump on a quick call. 

      Thanks,

      Steven