Hi,
I am using a sample TM1 server 'GO_New_Stores' to test the loading of a parameterized month load for specific and All months. But the value is not populating correctly for all the months when ran with parameter 'All'. Below is the code used in TI and attached is the source file and TI process file. I am using Planning analytics 2.04
Parameters:
pDivisons
pPeriod
Prolog:
cubname= 'Sales Plan';
dimDivisons= 'Divisons';
dimPeriod= 'Month_Dim';
Data Tab:
i=1;
dimsize = dimsiz(dimPeriod);
While(i <=dimsize);
elmperiod= dimnm(dimPeriod,i);
if(dtype(dimPeriod,elmperiod)@='N');
if(elmperiod@=pPeriod % pPeriod@= 'All');
cellputn(Quantity,'Sales Plan',pDivisons,Channels,Products,Versions,elmperiod,'Quantity');
cellputn(SalesReturnsandAllowances,'Sales Plan',pDivisons,Channels,Products,Versions,elmperiod,'Sales Returns and Allowances');
cellputn(vCashdiscount,'Sales Plan',pDivisons,Channels,Products,Versions,elmperiod,'Cash Discount');
cellputn(vVolumediscount,'Sales Plan',pDivisons,Channels,Products,Versions,elmperiod,'Volume Discount');
cellputn(vFreight,'Sales Plan',pDivisons,Channels,Products,Versions,elmperiod,'Freight');
endif;
endif;
i=i+1;
end;
Thanks in Advance!
Parameterized Data load issue
-
- Posts: 44
- Joined: Thu May 01, 2014 12:46 pm
- OLAP Product: Cognos TM1
- Version: 10.2
- Excel Version: 2010
Parameterized Data load issue
- Attachments
-
- test Load1.pro
- (3.16 KiB) Downloaded 198 times
-
- Sales plan2.csv
- (1.01 KiB) Downloaded 221 times
-
- Posts: 44
- Joined: Thu May 01, 2014 12:46 pm
- OLAP Product: Cognos TM1
- Version: 10.2
- Excel Version: 2010
Re: Parameterized Data load issue
The same value for the January month is getting populated for the other periods also when ran with the parameter 'All'. Can anybody tell where I am going wrong with the While loop. I tried to do the Asciioutput also but couldn't figure out the issue.
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: Parameterized Data load issue
Hi,
The fact that the same value is getting populated to the other periods is what I would expect given you are doing a while loop over the data tab. This is the main reason I would use a while loop on the data tab.
If we narrow this down to 2 rows, so rows 2 and 3 on your SalesPlan2 source file. And narrow it down to one column, Quantity.
Do you want the value 1969 or 398 to be put in every period if pPeriod = “All” or do you want the total of these values(1969+398) to be put in each period or something else?
cheers, Mark
The fact that the same value is getting populated to the other periods is what I would expect given you are doing a while loop over the data tab. This is the main reason I would use a while loop on the data tab.
If we narrow this down to 2 rows, so rows 2 and 3 on your SalesPlan2 source file. And narrow it down to one column, Quantity.
Do you want the value 1969 or 398 to be put in every period if pPeriod = “All” or do you want the total of these values(1969+398) to be put in each period or something else?
cheers, Mark
-
- Posts: 44
- Joined: Thu May 01, 2014 12:46 pm
- OLAP Product: Cognos TM1
- Version: 10.2
- Excel Version: 2010
Re: Parameterized Data load issue
Hi Mark,
Thanks for the reply.
Actually when I run the TI process for parameter value = Jan, it should sum up for the Jan period, and when ran for Feb month it should sum up for Feb period as per the Source. And apart from this when I run for the parameter 'All', the data should be populated for all periods doing the same approach when ran for single period. The issues I have are
1. When ran for the single parameter value e.g. Jan, the value is not summing until I use Cellincrementn. I don't understand why there is need to use cellincrementn if I am using the same file, instead of cellputn. E.g. Need for Jan (1969+398) for budget version1.
2. When ran for the parameter value= 'All', it should populate the values as per the source file. But for me the january data is only getting populated or the other period also which is not expected.
Attached are the screenshots.
Thanks in advance!!
Thanks for the reply.
Actually when I run the TI process for parameter value = Jan, it should sum up for the Jan period, and when ran for Feb month it should sum up for Feb period as per the Source. And apart from this when I run for the parameter 'All', the data should be populated for all periods doing the same approach when ran for single period. The issues I have are
1. When ran for the single parameter value e.g. Jan, the value is not summing until I use Cellincrementn. I don't understand why there is need to use cellincrementn if I am using the same file, instead of cellputn. E.g. Need for Jan (1969+398) for budget version1.
2. When ran for the parameter value= 'All', it should populate the values as per the source file. But for me the january data is only getting populated or the other period also which is not expected.
Attached are the screenshots.
Thanks in advance!!
- Attachments
-
- screenshot2.jpg (193.6 KiB) Viewed 3939 times
-
- screenshot1.jpg (161.53 KiB) Viewed 3939 times
-
- Posts: 48
- Joined: Thu Nov 19, 2009 10:38 pm
- OLAP Product: Tm1
- Version: 10.2.2
- Excel Version: 2010
Re: Parameterized Data load issue
Hi,
If you want it to work as per what you have now you need change elmperiod to the month variable in the source file (V6 I think you have for the month)
ie
if(elmperiod@=pPeriod % pPeriod@= 'All');
to
if(v6@=pPeriod % pPeriod@= 'All');
if(elmperiod@=pPeriod % pPeriod@= 'All'); resolves to true if you entered Jan as the parameter and so would populate for each month as you are looping over the months.
A bit unusual to loop over the months as you have done, unless you wanted to populate the same values as Mark mentioned.
As for using cellincrement vs cellput - cellput would result in the last record being populated, overwriting any previous cellput.
If you want it to work as per what you have now you need change elmperiod to the month variable in the source file (V6 I think you have for the month)
ie
if(elmperiod@=pPeriod % pPeriod@= 'All');
to
if(v6@=pPeriod % pPeriod@= 'All');
if(elmperiod@=pPeriod % pPeriod@= 'All'); resolves to true if you entered Jan as the parameter and so would populate for each month as you are looping over the months.
A bit unusual to loop over the months as you have done, unless you wanted to populate the same values as Mark mentioned.
As for using cellincrement vs cellput - cellput would result in the last record being populated, overwriting any previous cellput.
J.Rizk
Tm1 for everyone
Tm1 for everyone
-
- Posts: 44
- Joined: Thu May 01, 2014 12:46 pm
- OLAP Product: Cognos TM1
- Version: 10.2
- Excel Version: 2010
Re: Parameterized Data load issue
Thanks Rizk.
I have now modified the code, removed the while loop and data gets populated correctly by using cellincrementN only but cant I use CellputN to sumup the values to achieve the same result?
if(V6@=pPeriod % pPeriod@= 'All');
cellincrementN(Quantity,'Sales Plan',pDivisons,Channels,Products,Versions,V6,'Quantity');
cellincrementN(SalesReturnsandAllowances,'Sales Plan',pDivisons,Channels,Products,Versions,V6,'Sales Returns and Allowances');
cellincrementN(vCashdiscount,'Sales Plan',pDivisons,Channels,Products,Versions,V6,'Cash Discount');
cellincrementN(vVolumediscount,'Sales Plan',pDivisons,Channels,Products,Versions,V6,'Volume Discount');
cellincrementN(vFreight,'Sales Plan',pDivisons,Channels,Products,Versions,V6,'Freight');
endif;
Thanks in advance!
I have now modified the code, removed the while loop and data gets populated correctly by using cellincrementN only but cant I use CellputN to sumup the values to achieve the same result?
if(V6@=pPeriod % pPeriod@= 'All');
cellincrementN(Quantity,'Sales Plan',pDivisons,Channels,Products,Versions,V6,'Quantity');
cellincrementN(SalesReturnsandAllowances,'Sales Plan',pDivisons,Channels,Products,Versions,V6,'Sales Returns and Allowances');
cellincrementN(vCashdiscount,'Sales Plan',pDivisons,Channels,Products,Versions,V6,'Cash Discount');
cellincrementN(vVolumediscount,'Sales Plan',pDivisons,Channels,Products,Versions,V6,'Volume Discount');
cellincrementN(vFreight,'Sales Plan',pDivisons,Channels,Products,Versions,V6,'Freight');
endif;
Thanks in advance!
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: Parameterized Data load issue
Glad you got it working.
It should be noted that your process wasn't copying January values to every month but was copying the values in rows 5, 7 and 10 (so quantity for these rows is 5713). Why these rows? Because this is where your rows (For columns b:d) were unique.
Cellputn doesn't sum up values but replaces the value. Simple as that really
cheers, mark
It should be noted that your process wasn't copying January values to every month but was copying the values in rows 5, 7 and 10 (so quantity for these rows is 5713). Why these rows? Because this is where your rows (For columns b:d) were unique.
Cellputn doesn't sum up values but replaces the value. Simple as that really
cheers, mark