Forum Discussion

royari's avatar
royari
Contributor
3 years ago

MATRIX CONNECTOR MULTI YEAR LOAD

Hi- Our scenario is set up as Range ( say 2022M1 to 2024M12). I have a database table which have Jan to dec as columns and I have Year also as a column. So we have different year 2022, 2023 as rows and the data is in Jan to dec. IS there any way to load all of this together. Right now i can use a matrix data and put in a complex expression which can either say workflow time & "M1" etc or i can read a literal parameter read the year to be loaded and i can concat and return in the data source. But is there a way in data source complex expression or a parser business rule that I can get the value of another column  in the data source , so i can reference what is the field value of the year and the concatenate that.

Data Integration
  • royari's avatar
    royari
    3 years ago

    i resolved this by writing a union sql.

     

    Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M1') as 'Time' , Jan as 'Amount'

    from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

    UNION ALL

    Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M2') as 'Time', Feb as 'Amount'

    from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

    UNION ALL

    Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M3') as 'Time', Mar as 'Amount'

    from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

    UNION ALL

    Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M4') as 'Time' , Apr as 'Amount'

    from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

    UNION ALL

    Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M5') as 'Time', May as 'Amount'

    from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

    UNION ALL

    Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M6') as 'Time', Jun as 'Amount'

    from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

    UNION ALL
    Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M7') as 'Time', Jul as 'Amount'

    from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

    UNION ALL
    Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M8') as 'Time', Aug as 'Amount'

    from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

    UNION ALL
    Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M9') as 'Time' , Sep as 'Amount'

    from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

    UNION ALL

    Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M10') as 'Time', Oct as 'Amount'

    from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

    UNION ALL

    Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M11') as 'Time' , Nov as 'Amount'

    from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

    UNION ALL

    Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M12') as 'Time' , Dec as 'Amount'

    from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

     

  • Henning's avatar
    Henning
    Valued Contributor II
    3 years ago

    Hi Royari,

    May I ask you for a screenshot of your source file so that we can see the columns? How is the data loaded? Direct connection to the database table, file drop, (manual) CSV extract & load, ...? How did you set up the data source, in particular the time dimension?

    More details will help the community to better understand the issue.

    Best regards

    Henning

  • royari's avatar
    royari
    Contributor
    3 years ago

    Hi Henning, 

    There are 2 different methodology we have used to pull the data from the custom table. 

    1. Using Matrix - created 12 time dimension in the data source and map it to Jan, Feb, ...Dec columns respectively  (error - Scenario/time ID not mapped correctly)

    2. Using Tabular Format - Created 1 time dimension and map it with Year. We have written a complex expression where we're trying to pull the data based on the year but it did not worked. 

     

    Also attaching the screenshot of the custom table as requested by you.

     

    thanks, 

     

    • royari's avatar
      royari
      Contributor
      3 years ago

      i resolved this by writing a union sql.

       

      Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M1') as 'Time' , Jan as 'Amount'

      from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

      UNION ALL

      Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M2') as 'Time', Feb as 'Amount'

      from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

      UNION ALL

      Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M3') as 'Time', Mar as 'Amount'

      from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

      UNION ALL

      Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M4') as 'Time' , Apr as 'Amount'

      from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

      UNION ALL

      Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M5') as 'Time', May as 'Amount'

      from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

      UNION ALL

      Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M6') as 'Time', Jun as 'Amount'

      from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

      UNION ALL
      Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M7') as 'Time', Jul as 'Amount'

      from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

      UNION ALL
      Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M8') as 'Time', Aug as 'Amount'

      from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

      UNION ALL
      Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M9') as 'Time' , Sep as 'Amount'

      from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

      UNION ALL

      Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M10') as 'Time', Oct as 'Amount'

      from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

      UNION ALL

      Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M11') as 'Time' , Nov as 'Amount'

      from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

      UNION ALL

      Select RegisterID,Entity,Account,CONCAT(WFTimeName,'M12') as 'Time' , Dec as 'Amount'

      from XFC_PLP_Forecast where WFScenarioName = '4_32_Forecast'

       

  • NicolasArgente's avatar
    NicolasArgente
    Valued Contributor
    3 years ago

    The easiest would be to change the source. Can you add in the source the month like this :  Jan2022, Fev2023...
    Did you do your mapping correctly? You will need to map Jan in your case... 
    What is the error you get now? If there are any 🙂

    • royari's avatar
      royari
      Contributor
      3 years ago

      source file is a table with the structure I uploaded. I can either do one year at a time . But cannot do multiple years at the same time.  am i missing something , is this a simple sql ?