TM1 Forum

Hi, I'm trying to create a TI process that imports data from a text file, easy enough. The issue I'm having is that I need to transform one of the fields into 2, seperating the month from the year. I have this field vDate, sample value is 20160401121024. I need to transform that into the year '2016' and the month 'Apr-16'. To do this in the variable tab I've changed vDate to Other, added a new variable called vYear with the formula vYear=subst(vDate,1,4); and for the month I've used the following formula
vMonth=if(subst(vDate,5,2)@='01','Jan-'|subst(vDate,3,2),
if(subst(vDate,5,2)@='02','Feb-'|subst(vDate,3,2),
if(subst(vDate,5,2)@='03','Mar-'|subst(vDate,3,2),
if(subst(vDate,5,2)@='04','Apr-'|subst(vDate,3,2),
if(subst(vDate,5,2)@='05','May-'|subst(vDate,3,2),
if(subst(vDate,5,2)@='06','Jun-'|subst(vDate,3,2),
if(subst(vDate,5,2)@='07','Jul-'|subst(vDate,3,2),
if(subst(vDate,5,2)@='08','Aug-'|subst(vDate,3,2),
if(subst(vDate,5,2)@='09','Sep-'|subst(vDate,3,2),
if(subst(vDate,5,2)@='10','Oct-'|subst(vDate,3,2),
if(subst(vDate,5,2)@='11','Nov-'|subst(vDate,3,2),
if(subst(vDate,5,2)@='12','Dec-'|subst(vDate,3,2),''))))))))))));

When I look on the variable screen, it seems to resolve properly, that is, it shows Apr-16. The issue I'm having is that when I run the TI, I get an error message that says
Data Source line (1) Error: Data procedure line (20): Invalid key: Dimension Name: "Month", Element Name (Key): ""

It looks like the Month is not resolving like it shows in the variable tab

Any thoughts?

asciioutput the vdate field to see what's coming through.
Better, though, would be to set up aliases for the month dimension showing (eg) 0116 against Jan-16 - then you can just use subst(vdate, 3,4) for the month and you don't need your twelve nested ifs.
(Why is the year showing in the month dimension, btw?)

Thanks for the response. I knew I was going to get called out on the Year and Month-Yr dimensions . We'll either be removing the year dimension or modify the Month-Yr to be just Month Hadn't thought about the alias on the Month, I'll check that out.

When I do any asciioutput I just see " "

When I do any asciioutput I just see " "

Then you have a problem with your data source....

If the code you're having an issue with is in Prolog or Epilog, the variables are not available as they happen before and after the datasource is processed.

jwilkins wrote:If the code you're having an issue with is in Prolog or Epilog, the variables are not available as they happen before and after the datasource is processed.

The OP shows the error is seen in the data tab.
The error is a result of whatever is pulled through in the record for the date not having its substring as between 01 and 12. As David has suggested it is certainly an issue with the datasource which could be a number of things; due to it being on the very first record I would hazard an initial guess that it could be set to not skip the header rows... but only the OP can check the datasource and work out what it actually is.

If the code you're having an issue with is in Prolog or Epilog, the variables are not available as they happen before and after the datasource is processed.

Prolog variables are available in the Data tab - would be less than useful if they were not

David Usherwood wrote:Prolog variables are available in the Data tab - would be less than useful if they were not

Indeed. but what I think he meant is that you can't build a variable in the prolog that is based off anything in the data source. If the OP's code were in the prolog it would never work.

declanr wrote:The OP shows the error is seen in the data tab.

Ah, I must have glossed over that. Good catch. You are clearly much more observant than I am.

tomok wrote:but what I think he meant is that you can't build a variable in the prolog that is based off anything in the data source.

Exactly what I meant.

tomok wrote:

David Usherwood wrote:Prolog variables are available in the Data tab - would be less than useful if they were not

Indeed. but what I think he meant is that you can't build a variable in the prolog that is based off anything in the data source. If the OP's code were in the prolog it would never work.

Agreed, as you already hinted towards Tom the prolog executes before the data source runs so the variables defined within the data would never be available in prolog. I thought I'd mention that as I wasn't sure if the OP knew it. It's one of those that makes sense once you know.