MATRIX CONNECTOR MULTI YEAR LOAD

royari
Contributor

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.

1 ACCEPTED SOLUTION

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'

 

View solution in original post

5 REPLIES 5

Henning
Contributor III

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
Contributor

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)

Datasource.JPG

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.

 

CustomTable.JPG

thanks, 

 

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
Valued Contributor

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 🙂

Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

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 ?