TM1 Forum

Hi there,

Is there a way of dealing with null element values in a CSV file when importing in TI?

I have a CSV file with a column that is not always populated as it's not always relevant. The TI process runs but does not import the rows that have a null element - error is : Data Source line (21) Error: Data procedure line (4): Invalid key: Dimension Name: "SubNominal", Element Name (Key): ""

I would like to replace each null with something like "n/a" and have this as an element in the destination dimension, and I want to avoid having to do this in SQL before TI gets the file as this is to be a direct export from the source system.

Can it be done in the prolog section or metadata section with a rule?

I am quite new to TI code - I've had a good trawl round the forum and the TI user guide but I can't find anything specific that doesn't recommend doing it outside of TI.

Thanks
Steve

conflax wrote:Hi there,

Is there a way of dealing with null element values in a CSV file when importing in TI?

I have a CSV file with a column that is not always populated as it's not always relevant. The TI process runs but does not import the rows that have a null element - error is : Data Source line (21) Error: Data procedure line (4): Invalid key: Dimension Name: "SubNominal", Element Name (Key): ""

I would like to replace each null with something like "n/a" and have this as an element in the destination dimension, and I want to avoid having to do this in SQL before TI gets the file as this is to be a direct export from the source system.

Essentially you want an if statement that says when SubNominal is empty, set it to n/a. I think you're using the wizard right? You can achieve this by creating a new process variable and setting the formula to something like:
Then map your new variable to the dimension.

It's well worth looking at how this actually works by reading the code on the advanced tabs.

As asutcliffe posted above you can use IF statement to set variable to n/a. Alternatively if you can skip them all together by using IItemSkip in IF statement logic.

Thanks guys, got around it with a new process variable


Cheers
Steve