Conditional ASCIIOutput

[shinymcshires]

Fellow Forum Members:

I have a cube that I use to import transaction data from one system to our General Ledger. My current method is to slice a view from the cube, based on an attribute on a dim called "JEBatchNo". After I slice the data out to Excel, I save the file as "CRmmddyy" (CR is for cash receipts, along with the current date in mmddyy format). Due to restrictions with our General Ledger, imported file names have to be 8 alpha-numberic characters or less. What I'd like to try is the ASCIIOutput TI function, but I need the TI process to reference the "Imported" attribute on the JEBatchNo dimension (which I manually set to "Y" after I import that batch's data into the General Ledger). My first question is- where do I put this conditional statement? Is there a TI function that will lookup an attribute? Or would I need to think of the attribute as a dimension in the }ElementAttributes_JEBatchNo "cube"? In that case, would I use the CellGetS function? If that is correct, then I'm guesstimating that the correct syntax would be something like:

IF (CellGetS("ElementAttributes_JEBatchNo","JEBatchNo","Imported) @='')
ASCIIOutput(" 'CR' | (some function to return the current date in mmddyy format) | '.csv' ", "JEBatchNo", "JEBatchDesc".. and the remainder of the dimensions in cube order.

I hope this makes sense. I appreciate your time and thoughts!

[Martin Ryan]

Hi Shiny,

If I understand your question, you're trying to get an attribute using Turbo Integrator? The function you want is attrs(Dimension, Element, Attribute) for retrieving string attributes including aliases. Attrn is the equivalet for retrieiving numeric attributes.

Martin

[shinymcshires]

Martin-

Thanks for your input- So far, my syntax would be

IF (ATTRS("JEBatchNo","Imported") @='')
ASCIIOutput(" 'CR' | (some function to return the current date in mmddyy format) | '.csv' ", "JEBatchNo", "JEBatchDesc".. and the remainder of the dimensions in cube order.

ELSE

Some statement to exclude all other conditions from the ASCIIOutput function.


Any idea of which Advanced Tab I should put this procedure? My instinct tells me to put it in the Epilog, but I'm not sure.

I'm also wondering if it is possible to populate the current date in the ascii file name. Thanks again for your help!

[Steve Rowe]

Hello Shiny,

When you set the TI up do the following.

Set the data source to be the a cube view, you will need to build the cube view manually using the ASCIIExport (?) command on the right click menu from the cube. Define the view and save it but don't export it. Make sure that you ge tthe tick options right (exclude calculated / zeros / consolidations etc). It's usually much easier to set things up if you are expecting N level data.

Once you have this done goto the variables tab and set all the variables to other and make sure that all string / numeric attributes are set properly.

In the advanced section the prolog gets exectued once at the beginning before the data source is opened.
the Metadata is exectued once for every line of the data source and is usually used to update the structure
the data tab is excuted once for every line of the data after the meta data has been processed for every line. This is used to for everything else you want to do, often loading and exporting data.
The epilog is executed once at the end of the process and is usally used for tidying up after yourself.

So your code goes in the data tab.

To get the date stamp use Now and the Date functions, just take care you don't format the name of the file to something with extra "." in its as I have known this crash server.

HTH

[shinymcshires]

Steve-

Thanks for your input. I will give that a shot and let you know. I also thought of another step I should add. Once the file has been created, I should populate the "Imported" attribute with a "Y" for each JEBatchNo that was included in the ASCIIOutput function. Using the same ATTRS function, I could populate the null records with a "Y" with ATTRPutS, right? Thanks again!

[shinymcshires]

Steve-

I'm hitting a minor bump. I think my syntax may be wrong for the ASCIIOutput function:

jedata.JPG (71.91 KiB) Viewed 6384 times

I created the cube view as you had suggested, and instead of using the ATTRS function in the TI process, I thought I could just record an expression for that specific dim (JEBatchNo) that selected elements by attribute (selecting data where the "Imported" atttribute is set to null).

In the event that this is helpful, this is the Variables tab-

jevariables.JPG (101.68 KiB) Viewed 6384 times

This is the error I get when trying to run the TI process:

jerror.JPG (16.73 KiB) Viewed 6383 times

Thanks again for looking into this. I appreciate it.

[Alan Kirk]

Steve-

I'm hitting a minor bump. I think my syntax may be wrong for the ASCIIOutput function

I created the cube view as you had suggested, and instead of using the ATTRS function in the TI process, I thought I could just record an expression for that specific dim (JEBatchNo) that selected elements by attribute (selecting data where the "Imported" atttribute is set to null).

In the event that this is helpful, this is the Variables tab-

This is the error I get when trying to run the TI process:

Thanks again for looking into this. I appreciate it.

What you're getting isn't a run time error, but a compilation error; that is, a problem with the syntax of your code.

When you use AsciiOutput, all of the fields that you export need to be strings, not numeric values. You therefore have to use either the Str() rules function or, if you're on a late enough version, NumberToStringEX to convert the Value argument into a string.

[shinymcshires]

Alan-

Thanks for your helpful suggestion. I changed the Value variable using the numbertostring function:

numbertostring.JPG (105.77 KiB) Viewed 6374 times

I also revised the ASCIIOutput function a bit to use the new Value_Var variable:

revisedjedata.JPG (74.87 KiB) Viewed 6373 times

I think I have the file parameter syntax wrong for the ASCIIOutput function wrong- I'm trying to concatenate the current date as the file name in the UNC, but I don't have it quite right:

ASCIIOutput(' "\\millbrae1\common\finance\richard\je" | date(now) | ".csv" ',JEBatchNo,JEBatchDesc,JEBatchDate,JETranCode,JERef,JETranDesc,JETranDate,
JEAcctNo,Value_Var);

Here's the error log file:

"CR","105","02","2009","C1050209","CR Batch 105.02.2009","02052009","JE","CR JE 17","Rec Contract Classes P & R","02052009","1215-0000-101100-000","227.",Data Source line (1) Error: Data procedure line (7): File " "\\millbrae1\common\finance\richard\je" | date(now) | ".csv" " not found.




Thank you!

[shinymcshires]

Oops. Just noticed that the Value_Var variable is not set to String. I've changed that now.