CELLPUTN

[Mithun.Mistry1103]

Hello

I have been able to post 1 against two dates, Jan-15, Dec-34 via ti, but how do I put 1 against any dates between Jan-15 and dec-34 as they are strings, and im sure you cant do <= and => between strings.

Can anyone offer any advise to what I could do to get round this issue?

Thanks in advanced

Mithun

[Wim Gielis]

Dear friend,

This is not an enhancement, so you are posting in the wrong subforum.
You registered to this forum more than 3 years ago, so this should not be new to you.
I assume this topic will be moved shortly.

and im sure you cant do <= and => between strings.

True, but you can use @<= and @=> when you compare textual values - given that the strings are built up such that alphanumeric comparisons are correct.

Other than this, use a normal WHILE() End loop to loop over dates. Not knowing the design of your cubes and dimensions,
this is about the best answer you can hope to receive.

[Mithun.Mistry1103]

Hello Wim

Apologies for that. I will ensure that I dont do that again

In regards to your response to the problem, thank you for the suggestion. I thought of doing a while loop, the only thing that discouraged mewas the following details:
My script is:

Code: Select all

#vstartdate=cellgets();
#vfinishdate=cellgets();

vstartdate = cellgetS();
while ( vstartdate @=> vMonthDim);
cellputN(STRINGTONUMBER('1'), .. , vstartdate, ..);
End;

It would not recognise @=> and secondly, the question comes to mind is, how do i say, stop putting 1 into intersections after vFinishDate and if the vstartdate is '', just start putting 1 from the first level 0 node in the vMonthdim.

In terms of the cube and dimension designs, there are 6 common dimensions, the 1 dimension that is different, for cube 1, its a month dim, with Jan-15 etc periods as 0 level nodes and in cube 2, the startdate and finishdate is string values within the nodes intersections.

I will continue to work on it.

Thank you once again sir.

[Wim Gielis]

If you compare strings these should be in the same format.
For example: yyyymmdd on both sides of the inequality.

Can you provide more information ?
What business problem do you want to tackle ?
Which cubes do you have ? Which dimensions ?
Besides the looping, do you use a view for the data source of the process ? Or a different data source ?
Provide relevant screenshots of dimension elements.
Etc.

[Mithun.Mistry1103]

Hello Wim

The format is the same - both strings and Month Period nodes.

Cube 1 -
dim1 - UniqueID
dim2 - Place
dim3 - Type
dim4 - unit
dim5 - model
dim6- Measure
Dim7 - Details ( 2 elements, startdate (Intersection: Jan-15), finishdate (intersection: Dec-34))

Cube 2 -
dim1 - UniqueID
dim2 - Place
dim3 - Type
dim4 - unit
dim5 - model
dim6- Measure
Dim7 - Months (Jan-15, ..,, ... , Dec -34)

Just using data views from cubes.

I am attaching a similar format to what I am currently looking at with all referred dimensions.

Thank you for the help

[Wim Gielis]

It complicates matters if StartDate and/or FinishDate is empty, but if they were both non-empty or both empty, then this will work:
Create a view on Cube1 in the Prolog section of the Advanced tab. Select in the dimension Details on element StartDate.
That way, you can loop over all non-zero StartDate cells
Also, do a zero out on Cube2 for the relevant cells

In the Data tab of the Advanced tab, write:

Code: Select all

i = Dimix( 'Months', SValue);
While( i <= Dimix( 'Months', CellGetS( 'Cube1', UniqueID, Place, Type, unit, model, measure, 'FinishDate') ));
   CellPutN( 1, 'Cube2', UniqueID, Place, Type, unit, model, measure, Dimnm( 'Months', i ));
   i = i + 1;
End;

This further assumes:
- input in Cube2 has the same month names as in dimension Months can be found
- the months in dimension Months are sorted, index-wise, from the oldest (index 1) to the newest month (highest index). If this is the case, and you can live with the fact that indexes in that dimension should ALWAYS be correct, this will work without further changes.

Honestly, I do not know why you would write:

Code: Select all

cellputN(STRINGTONUMBER('1'),

Instead of

Code: Select all

cellputN(1, 

Why is that ?

[JulianS]

Hi Mithun, Im guessing you want to turn this

Capture1.PNG (12.87 KiB) Viewed 11436 times

Into this?

Capture2.PNG (46.28 KiB) Viewed 11436 times

Have you considered doing it in TM1 rules?

I think the main thing you need is give your time dimension a number attribute that it can use for period checking rather strings see below for an example

Capture8.PNG (39.62 KiB) Viewed 11436 times

[declanr]

Rules are a possible option but if it needs to be fed then doing so will at best be annoying and at worse a huge memory drain (due to likely over feeding.)

On the TI approach I would avoid using the dimension index due to the issues mentioned above, even if the order is correct now it is easy for someone to mess it up.

As long as you have a "NextPeriod" attribute populated then it's a simple loop:

Code: Select all

sTargetPeriod=CellGetS('Cube1',vDim1...,'StartDate');
sEndPeriod=CellGetS('Cube1',vDim1,...,'EndDate');
While ( sTargetPeriod @<>'');
       CellPutN(Value,'Cube2',vDim1,...,sTargetPeriod);
       If ( sTargetPeriod @= sEndPeriod );
               sTargetPeriod = '';
       Else;
               sTargetPeriod = Attrs ('PeriodDim', sTargetPeriod, 'NextPeriod');
        EndIf;
End;

Also your post suggests that you don't have a measures dimenion in cube 2, I won't go in to it too much here as it's done to death in this forum but I would certainly reconsider it if I were in your position.

[JulianS]

Have you considered doing it in rules, that way you dont have to run a TI after every change.

Here are the cubes i've created based upon yours.

Capture7.PNG (6.62 KiB) Viewed 11434 times

And here are the rules for each of the cubes.

Capture4.PNG (19.15 KiB) Viewed 11434 times

Capture3.PNG (49.45 KiB) Viewed 11434 times

It may look a bit complicated, but its really good and makes TM1 faster as it doesn't feed cells it doesn't need to and as it looks like your using a lot of from and too dates that may be a good thing. it depends on how you feel about conditional feeding.

What happens is that when you change one of the dates in your Start End cube the string change always fires the feeders (unlike numeric cells).

The feeder section on a change of start date or end date fires forward from the start date and backwards from the end date but only where it falls in the range, so its all automatically fed.

What complicates it more is that you are using blank, so i've used as a default

start date blank = from the first period,
end date blank = to the last period.

[JulianS]

So the proof of the pudding is

Capture6.PNG (52.85 KiB) Viewed 11432 times

Where you can see the zero is unfed, which means that its not going to feed any other cubes and waste CPU.

When we suppress zeros we get.

Capture9.PNG (18.44 KiB) Viewed 11432 times

Which is still correct.

If you still want to do it in TI give me a shout, it will be quite easy to go through it in TI.

[JulianS]

In TI it's easier as Declain says, here is a starter for 10 that should put you on the right track, i've used bedrock to create the view that its going to use,

Based upon the dimension we discussed earlier.

Capture10.jpg (140.29 KiB) Viewed 11295 times

Can be achieved quite simply by using the following TI process

Capture11.jpg (57.39 KiB) Viewed 11295 times

And the following code

Capture14.jpg (170.9 KiB) Viewed 11295 times

Its just a simple example and you will need to add all your other dimensions in, but hope it will be a starter for 10.

Regards Jules

[Wim Gielis]

Jules,

Thank you for your contribution.
Why do you use the destination cube as the data source ?
To me it makes more sense to use the input cube as the data source. With an extra measure (Treated, 0 or 1) and a simple loop as explained above, this is not difficult to do.

[JulianS]

Hi Wim,

Thats quite easy to explain, its for simplicity for mithun to follow.

We are using a TI view instead of loops, whilst also dealing with :

Mithuns blank in 'Start Date' meaning from the first period of the dimension and
Mithuns blank in 'End date' being the end of the dimension

This can also be completed in a number of ways and as the old saying goes, there are many ways to skin a cat.

You have insisted on another measure element or dimension having a zero or a one?

Why is that?

I have also noticed your negativity towards users

Why is that?

Could it possibly because its easier to be be a critic than a creator?

[JulianS]

Hi Mithun,

Here is a example that doesn't need any extra measures as its all calculated in the TI.

Its a bit more complicated than the previous one, but Wim was having issues with understanding the logic of using a view of the target cube to iterate through all the cells
and process them in turn.

So here is a quick TI I knocked together to help you doing it from the Wims suggestion (but hopefully a bit more constructively for you).

Cube Mithun_Start_End -> Mithun Periods

No new measures are required.

Capture20.jpg (252.18 KiB) Viewed 11191 times

Capture22.PNG (16.11 KiB) Viewed 11191 times

And to quote our friend ' Its not difficult to do'

Good luck my friend, and happy TM1ing in the future!

[Wim Gielis]

I have also noticed your negativity towards users

Could it possibly because its easier to be be a critic than a creator?

JulianS,

Always funny, people like you. You registered 3 days ago, so please make comments only once you get to know the environment a little bit. And do learn some "netiquette" please.

I gathered more than 1,400 posts here. I gathered another 40,000 posts on public Excel and VBA forums. All for free.
I have been a Microsoft Most Valuable Professional, 4 times consecutively, for apparently not creating anything and for not boosting the communities I got the awards for being the best-in-class giving critique
There is also my public website with multiple hundreds of (lengthy) Excel and TM1 articles, both English and Dutch, and code.
All for free and I guess - please do speak up if it's not correct - that I created something.
Check out the "Useful code, tips and tricks" subforum (http://www.tm1forum.com/viewforum.php?f=21): I invite you to create as much as I did and still do. The last 3 topics are mine with 2,600 lines of TI coding, excluding whitespace. But I don't create

The bottom-line goes like this: please behave yourself or you will be kicked out soon.

On-topic:
even with no single input in your input cube, you will loop over ALL lowest-level cells in the target cube. You do understand the function "ViewExtractSkipZeroesSet" ?
Even though it's not as simple as your approach, I agree, you should know that simplicity comes with a cost.
If TI is used in this case, it has to be quick since users will execute the process quite often.
If you want to loop over all those potentially hundreds of thousands of cells, which is even not necessary: good luck.

Its a bit more complicated than the previous one, but Wim was having issues with understanding the logic of using a view of the target cube to iterate through all the cells and process them in turn.

Absolutely, my TM1 knowledge is way below average, I do apologize for that. I am taking courses to get up to speed with the software

And to quote our friend ' Its not difficult to do'

Apparently it was too difficult for you because your ElseIf in the Data tab can be flat out wrong

For your information. What you failed to understand, is that I would create a view on the input cube. Seems logical: if no input, then nothing to be processed and the process quits soon. But if both start date and end date are part of the source data, you could treat a "combination" twice. Therefore, set a measure to 1 to keep track of the start dates (or end dates) you already treated. I agree it's not as simple as your solution, and I value simplicity too, but not at the cost of functionality (not the case here) or at the cost of processing many cells that are just not needed. Do you understand the mechanics of cubes and that the number of combinations / cells runs into millions quickly ? You will soon change your mind once you set up allocations in a TM1 model for real and you start from the target cube Happy looping! Above you wrote about not wanting to waste CPU

Finally, if you want to be constructive, post code and not only screenshots. Do users need to type code again ?

[JulianS]

Dear Wim,

Many thanks to your insight and your knowledge and your off topic points, all duly noted.

I would query a few points you raised though.

You Quote :

Always funny, people like you. You registered 3 days ago, so please make comments only once you get to know the environment a little bit. And do learn some "netiquette" please.

Some people who work as developers do not post on this forum but would like to help people and not just be mean.

In my career I have worked very closely with Steve Vincent i'm sure you know him as you congratulated him on his postings.

I however have always avoided this forum as i think its confrontational.

For example just look at this thread to see the negativity. Some of us want to help people, not slap them down and made to feel foolish for asking for help.

You Quote:

Check out the "Useful code" subforum here: I invite you to create as much as I did and still do.
Please behave yourself or you will be kicked out soon.

That's very noble and commendable, and is an asset to us all.

I only signed up three days ago as I was contacted directly by Mithun an old colleague of mine from years ago regarding the issue as he wasn't getting anywhere on here and was not following the logic, otherwise I wouldn't have joined.

You Quote

Please behave yourself or you will be kicked out soon.

Ok, what am I supposed to have done now? Help somebody? Help solve somebodys issue? Is that grounds for being kicked out.
If helping somebody is grounds for kicking somebody out then so be it.




On Topic

You Quote

For your information. What you failed to understand, is that I would create a view on the input cube. Seems logical: if no input, then nothing to be processed and the process quits soon. But if both start date and end date are part of the source data, you could treat a "combination" twice. Therefore, set a measure to 1 to keep track of the start dates (or end dates) you already treated. I agree it's not as simple as your solution, and I value simplicity too, but not at the cost of functionality (not the case here) or at the cost of processing many cells that are just not needed. Do you understand the mechanics of cubes and that the number of comnbations / cells runs into millions quickly ? You will soon change your mind you set up allocations and start from the target cube

Let me take this point by point

1. What you failed to understand, is that I would create a view on the input cube. Seems logical: if no input, then nothing to be processed and the process quits soon.

A: I think your assumptions are wrong if you look at the data, and the TI, suppress zeros are deliberately turned off therefore if no start date and no end date are in place, all rows would be 1, therefore we would need to traverse the entire dimension setting them to 1.
This is how it it has been written to work.

But if both start date and end date are part of the source data, you could treat a "combination" twice. Therefore, set a measure to 1 to keep track of the start dates (or end dates) you already treated. I agree it's not as simple as your solution, and I value simplicity too, but not at the cost of functionality (not the case here) or at the cost of processing many cells that are just not needed.

Thank you for your complement, but please see above answer. if your assertion is true you would only be able to process a record where either a start date or end date is present. In this case, I think you'll find this is an employee, where if no start date or end date is supplied they they will be working for all periods until TM1 is instructed otherwise. This is taken care of in both the TI processes. If however the data always had a start or end date the Ti would be as you say.

Do you understand the mechanics of cubes and that the number of comnbations (sic) / cells runs into millions quickly ?

A : I surely do. Its a good job TM1 only holds non zeros in memory huh? Otherwise we'd all be in trouble.

You will soon change your mind you set up allocations and start from the target cube

A: Which TI are you talking about the one I generated as a simple example? That was just a demonstration to show the logic as explained above.

I'm sorry if you think that my helping another person is wrong, but that's the way it is.

I'm sorry that you feel so upset but I do not know why.

Kind Regards

Julian

[Wim Gielis]

Dear Julian,

It's humble that you help others, we all do. Not only during the day, also in the evening and during weekends. However, your negative comments addressing me personally in this topic are very unprofessional and even low-level. I leave it to the moderators to decide how to rate these picky comments.

What's your point about having worked with Steve Vincent ? That you don't make picky comments ?

On topic: you deliberately turn off skipping empty cells. You assume that empty starting date and empty ending date means: all time periods should be 1. I would assume that all periods should be 0 in that case. A cube with 7 dimensions can have a lot of empty cells and combinations that are not "occupied" business-wise - these are all employees that work all periods in your understanding ? In your simple 2D cube you won't see the effect of not skipping empty cells. The real cubes are not 2D however.

Wim

[JulianS]

Dear Wim,

It's humble that you help others, we all do. Not only during the day, also in the evening and during weekends.

Agreed. But we should also encourage people not feel worried to post their issues.

However, your negative comments addressing me personally in this topic are very unprofessional and even low-level.

Well thats a two way door to get respect you have to show respect.
Please explain how I've personally hurt your feelings.

Please explain why my comments are low level compared with say.

Honestly, I do not know why you would write:

CODE: SELECT ALL
cellputN(STRINGTONUMBER('1'),


Instead of

CODE: SELECT ALL
cellputN(1,


Why is that ?

or :

Thread Planning Analytics Local 2.0: Rules Calling Multiple Hierarchies
in which you say

RTFM

I leave it to the moderators to decide how to rate these picky comments.

Thats fine with me. If they kick people out for trying to help then thats ok.

What's your point about having worked with Steve Vincent ?

Some of us have worked with TM1 for a long time and have not posted.
I've worked with Steve on and off for 10 years in TM1 on lots of projects.
The three days you quote are not really relevant.

Your above quote

Always funny, people like you. You registered 3 days ago, so please make comments only once you get to know the environment a little bit. And do learn some "netiquette" please.

Some of us have kept an eye on this forum even though we don't post.

That you don't make picky comments ?

I've already told you in the previous note that i'm sorry that i've hurt your feelings.

On topic:

you deliberately turn off empty cells. You assume that empty starting date and empty ending date means: all time periods should be 1. I would assume that all periods should be 0 in that case.

No that the crux of the issue, having actually spoken to Mithun, this is the behaviour he wants.

The TI above is an 'example' and is using two dimensions because its using a demo cube with two dimensions.
When you change it for an cube with more dimensions, you would adjust your view accordingly, say for example a scenario dimension were to be added, we would use a single 'N' level in the cube view, very rarely would you run an update TI against an entire seven dimensional cube as you could inadvertently effect other versions. I would also not be using CubeClearData, but ViewZeroOut with a bespoke view zero out view all of which would have parameters.

I think the issue here is that these are examples to show functionality, not finished products, as I said in the post 'Starter for 10'.

A cube with 7 dimensions can have a lot of empty cells and combinations that are not "occupied" business-wise - these are all employees that work all periods in your understanding ?

In the two dimensional cube yes, but as discussed above on a cube with more dimensions you would tailor you view accordingly and only run the TI against a 'slice' of the cube and you would be using a ViewZeroOut and a bespoke
view.


Look i'm not going to post any more on this thread as I feel its now as i't become something it shouldn't have.

[Mithun.Mistry1103]

Hello

Thank you so much for all your help guys. I have learnt alot technically and I have received so much support from you all.

Jules, thank you so much for coming onto the forum on my request, I appeciate all the help you've given me. I have done both solutions and they both work. Again, thank you so much.


Mithun

[declanr]

Jules, thank you so much for coming onto the forum on my request, I appeciate all the help you've given me. I have done both solutions and they both work. Again, thank you so much.

Happy to hear that your problem has been solved.

Just to offer an alternative to the rule approach to any future users that stumble across this thread which may hopefully be easier to follow as it just focuses on populating the first period and then rolling on until the end period is found.
I caveat this by saying you should probably throw a rule at the start to force all cells to zero if the End Date is not populated.

Also with rules like this you need to be careful that you don't go past the stack overflow point; it is unlikely you will when working with a month based dimension (assuming that all actuals are done by STET'd TI values and it only calculates forecast) but if it went down to week or day level, or if you are dealing with showing the "active life" of long-term construction projects etc (we had some that exceeded 20 years at a previous company I worked with) then you could blow it.

Rule in Cube 2

Code: Select all

[]=N:
	# First populate a 1 if the Month is equal to that held in "Start Date" in Cube 1
	If ( DB('mithun_1',!mithun_id,'Start Date') @= !mithun_time,
		1,
		# If the previous month from this one is equal to that held in "End Date" in Cube 1 then set to zero
		If ( AttrS ( 'mithun_time', !mithun_time, 'Prior_Period' ) @= DB('mithun_1',!mithun_id,'End Date'),
			0,
			# Otherwise pull value from the previous month (1 or 0)
			DB('mithun_2',!mithun_id, AttrS ( 'mithun_time', !mithun_time, 'Prior_Period' ) )
		)
	);

Feeder in Cube 2:

Code: Select all

[{'Start Date','End Date'}] => DB('mithun_2',!mithun_id, DB('mithun_1',!mithun_id,'Start Date'));

Feeder in Cube 1:

Code: Select all

[{'Start Date','End Date'}] => DB('mithun_2',!mithun_id, DB('mithun_1',!mithun_id,'Start Date'));

I will also add my usual apologies for any typos as this hasn't been tested in a model and instead just typed here but hopefully the gist is there.