The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

royari's avatar
royari
Contributor
4 years ago
Solved

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 a...
Data Integration
  • royari's avatar
    royari
    4 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
4 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
    4 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 ?