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 a...
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'

     

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'