Create an Array from a Date String 1

[AnthonyMJ]

Sample Data Below

Item                Days
----+-----------------------------------+
A    2009-01-10, 2009-01-15, 2009-02-14
B    
C    2009-03-24, 2009-04-23
D    2009-05-20

As you can see, the Days column may not contain anything or may contain 3 Days or more.

I would like to create an array for the Days so I can reference them in other formulas.

This is what I have so far. I am getting a "A subscript must be between 1 and the size of the array" error.

I have checked for Days that are not null and included an array limit but that doesn't seem to work.

Any ideas ?

local numbervar limit:= ubound(split({Days},','));
local stringvar array dates;
local numbervar x;

if Not IsNull({Days}) then (
   for x := 1 to limit do (
       dates [x] := split({Days},',')[x];
   );
   dates[x];
);

XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[Turkbear]

Hi,
Try using a test for Ubound = 0 instead of Not IsNull:

local numbervar limit:= ubound(split({Days},','));
local stringvar array dates;
local numbervar x;

if limit > 0 then (
   for x := 1 to limit do (
       dates [x] := split({Days},',')[x];
   );
   dates[x];
);

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[AnthonyMJ]

Tried that too..That didn't work. Still getting the same error.


XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[lbass]

Try:

numbervar limit:= ubound(split({Days},','));
stringvar array dates := "";
numbervar x;

if Not IsNull({Days}) then (
for x := 1 to limit do (
redim preserve dates[limit];
dates [x] := split({Days},',')[x];
)
);

This formula will return true, but it is establishing the values in the dates array, which you can then reference in other formulas. Since you are planning to reference the results in other formulas, the variables should be global (default when not specified), not local. I think you should also provide a value to the dates string array so that it will reset for each record--even when it encounters nulls.

-LB

 

[AnthonyMJ]

Thanks. I have created the formula you suggested and it returned TRUE. Then, before I reference the array in other formulas, I just want to check and see day values in the dates array and my formula doesn't show anything.


//{@Day1}
global stringvar array dates;
dates[1];

Also, changed the dates[1] to dates[2] and got the subscript issue again.


XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[lbass]

Please show the formula as you wrote it and also explain where you placed it in the report. If it still doesn't work, try adding: whileprintingrecords at the beginning of each of formula. Your reference formula should be set up like this, e.g.,:

whileprintingrecords;
stringvar array dates;
if ubound(dates) >= 2 then
dates[2];

You shouldn't be getting the error message though. I didn't when I tested it.

-LB

 

[AnthonyMJ]

I placed the reference formula at the right side of the CreateArray formula

Here's how the report looks like :-

Item                Days                 CreateArray   Day1
----+-----------------------------------+-----------+-----------+
A    2009-01-10, 2009-01-15, 2009-02-14   True
B                                         True       
C    2009-03-24, 2009-04-23               True       
D    2009-05-20                           True

Here are the formulas created :-

//{@CreateArray}
numbervar limit:= ubound(split({Days},','));
stringvar array dates := "";
numbervar x;

if Not IsNull({Days}) then (
for x := 1 to limit do (
redim preserve dates[limit];
dates [x] := split({Days},',')[x];
)
);

//{@Day1}
whileprintingrecords;
stringvar array dates;
if ubound(dates) >= 1 then
dates[1];

{@Day1} still comes out as null. By the way, you said in your previous post to "also provide a value to the dates string array so that it will reset for each record--even when it encounters nulls". What default do I have to give (any date?) and where do I have to place it, footer ?


XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[lbass]

I added the "" value to the dates string array, so that should be okay. I'm not sure whether the null is the problem or whether it might be a timing issue. Try making the following changes:

//{@CreateArray}
numbervar limit:= ubound(split({Days},','));
stringvar array dates := "";
numbervar x;

if isnull({Days}) then
"" else
if Not IsNull({Days}) then (
for x := 1 to limit do (
redim preserve dates[limit];
dates [x] := split({Days},',')[x];
)
);

//{@Day1}
evaluateafter({@CreateArray});
stringvar array dates;
if ubound(dates) >= 1 then
dates[1];

-LB

 

[AnthonyMJ]

The only thing I did was to change the {@Day1} to include the evaluateafter({@CreateArray}) and that did the trick.

Thanks a lot for your help.

XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g