TM1 Forum

I have a rather simple csv file that I'm bringing into TM1 via TI. The file has 5 fields; AccountID, State, Segment, AccountNumber, AccountName. AccountID is the Element. State and Segment are Consolidations and AccountNumber and AccountName are Attributes/Alias for AccountID.

The basic premise is that within each state there are 4 segments (01, 02, 03, 04). Each segment has numerous accounts belonging to it. Each state has 4 segments. See below for example. When the upload is "successfully" complete the State-Segment relationship looks fine. However when I drill into the segments of a sigle state the 01 segment references all of the accounts that referenced a 01 segment regardless of which state it belongs to. Same for the 02, 03 qnd 04 segments. Go to the next state it's the same problem.

What do I need to do so that the process only places the accounts under their proper segment-state combination? I've attached a word doc with images of what my TI screens look like if that helps at all.

Thanks,
Tony
Thanks,
Tony

cardantim wrote:I have a rather simple csv file that I'm bringing into TM1 via TI. The file has 5 fields; AccountID, State, Segment, AccountNumber, AccountName. AccountID is the Element. State and Segment are Consolidations and AccountNumber and AccountName are Attributes/Alias for AccountID.

The basic premise is that within each state there are 4 segments (01, 02, 03, 04). Each segment has numerous accounts belonging to it. Each state has 4 segments. See below for example. When the upload is "successfully" complete the State-Segment relationship looks fine. However when I drill into the segments of a sigle state the 01 segment references all of the accounts that referenced a 01 segment regardless of which state it belongs to. Same for the 02, 03 qnd 04 segments. Go to the next state it's the same problem.

What do I need to do so that the process only places the accounts under their proper segment-state combination? I've attached a word doc with images of what my TI screens look like if that helps at all.

Thanks,
Tony

Code: Select all

Connecticut
  01
    Stamford
    Greenwich
  02
    Hartford
    Wolcott
  03
    Groton
    Ledger
  04
    Saybrook
    Mystic
New York
  01, 02, 03, 04
New Jersey
  01, 02, 03, 04

It isn't a "problem", just the way the system works.

When you create a consolidation such as the Segment one (01, 02, 03, 04) then it exists independently of any parent element that it may be attached to. That is to say, the consolidation 01 stands by itself. It's not "New York's 01", or "Connecticut's 01", it's just a consolidation named "01" which can be added to any other consolidation as a child, or just left as a top level consolidation come to that.

Consequently when you add children to one of those segment consolidations (the accounts, in your case) then those elements are children of the segment. They will appear if you drill down under any of the state consolidations, not just the ones that you had in mind.

Now that having been said... if the account doesn't apply to a particular state's segment (that is, it has no data) and you have a zero suppressed view, the row won't actually appear in the view that you create. in other words if Segment 01 has the accounts A, B, C and D but only D applies to Noo Joisey (sorry Jim) then all you'll see in the view is: When you're drilling down in the subset editor you'll still see But only D will appear in the view.

(Aside: When you're making a post and want to keep space indents, wrap it in a Code block using the button at the top of the post form as I've done above (and modified your original post to do). Otherwise the spaces get stripped out. This isn't a forum bug but is a characteristic of HTML which I won't go into here.)

If the appearance of the accounts in the subset editor is still a problem for you, the best option that you have is to use segments which are unique to each state. For example, call New jersey's ones NJ01, NJ02 and so on. In that way you can assign only the accounts which are specific to that state to that state's segment.

However I note from the document that you're using the automated code wizard; you'd need to break away from that and start hand-crafting your own code (which I do recommend anyway) to do this.

Thanks Alan. I figured out my problem while I was looking at my different levels. I corrected by implementing StateAbbr+Segment (NY-01, NY-02...) as the segment values. Still fairly new to TM1 so I expect that eventually I'll get to hand crafting my own code.