06-09-2022 05:29 PM - last edited on 02-07-2023 10:07 AM by akloepfer
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.
Solved! Go to Solution.
06-10-2022 05:17 PM
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'
06-10-2022 03:02 AM - edited 06-10-2022 03:39 AM
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
06-10-2022 07:16 AM
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,
06-10-2022 05:17 PM
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'
06-10-2022 11:50 AM
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 🙂
06-10-2022 03:50 PM
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 ?