Dealing with nulls in TI

[conflax]

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

[asutcliffe]

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:

Code: Select all

vSubNominalNew = If(SubNominal @= '', 'n/a', SubNominal);

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.

[EvgenyT]

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.

[conflax]

Thanks guys, got around it with a new process variable


Cheers
Steve