Parameterized Data load issue

Post Reply
krithika331
Posts: 44
Joined: Thu May 01, 2014 12:46 pm
OLAP Product: Cognos TM1
Version: 10.2
Excel Version: 2010

Parameterized Data load issue

Post by krithika331 »

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!
Attachments
test Load1.pro
(3.16 KiB) Downloaded 198 times
Sales plan2.csv
(1.01 KiB) Downloaded 221 times
krithika331
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

Post by krithika331 »

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.
Mark RMBC
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

Post by Mark RMBC »

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
krithika331
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

Post by krithika331 »

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!!
Attachments
screenshot2.jpg
screenshot2.jpg (193.6 KiB) Viewed 3938 times
screenshot1.jpg
screenshot1.jpg (161.53 KiB) Viewed 3938 times
jrizk
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

Post by jrizk »

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.
J.Rizk
Tm1 for everyone
krithika331
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

Post by krithika331 »

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!
Mark RMBC
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

Post by Mark RMBC »

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
Post Reply