Nested Transformation with IF condition

Hello,

I have two dates (Date format) Invoice date and Posting date.POSTDT, INVCDT

I have a logic to update if the Invoice date is null then update posting date else Invoice date and if both the dates are null then *Str(No_Date)

here is my transformation -

TIME=*IF(POSTDT+INVCDT=POSTDT THEN POSTDT;POSTDT+INVCDT=*STR() THEN *STR(NO_DATE);INVCDT)

I get reject records - TIME= 00000000

So i update the transformation as below -

TIME=*IF(POSTDT+INVCDT=POSTDT THEN POSTDT;POSTDT+INVCDT=*STR(00000000) THEN *STR(NO_DATE);INVCDT)

Still i get reject records as TIME = 00000000

so kinda lost ... anything missing?

10|10000 characters needed characters exceeded

Sep 22, 2017 at 06:17 PM

Then the correct string will be (slightly corrected version of Lucas answer):

TIME=*IF(ZINVOCEDT+ZF_DPOST=*STR(0000000000000000) THEN *STR(NO_DATE);ZINVOCEDT=*STR(00000000) THEN ZF_DPOST;ZINVOCEDT)

ZINVOCEDT can be 00000000 or correct time value

ZF_DPOST can be 00000000 or correct time value

By the way it's a bad idea to have NO_DATE member in TIME dimension (if you don't have 3 level hierarchy for this member)

P.S. Absolutely strange conversion file! Why not to use single line JavaScript conversion instead of long table?

10|10000 characters needed characters exceeded

There are strict rules how to design TIME dimension. I have explained it many times. Please search!

• Sep 22, 2017 at 02:08 AM

This + sign will not sum up the dates. The transformation actually does a string operation. So you're basically concatenating the dates in there.

This kind of transformation would be better of in BW.

10|10000 characters needed characters exceeded
• The topic author is talking exactly about sting concatenation!

• Sep 22, 2017 at 06:01 AM

10|10000 characters needed characters exceeded

Here is the data in the cube from RSA1 -

When Invoice Date is null -

Conversion file example for one year, same logic extended for all the years-

scn9.png (4.9 kB)
scn10.png (20.2 kB)
scn11.png (4.1 kB)
• Sep 22, 2017 at 08:52 AM

Hi, give it a try to the statement below:

TIME=*IF(INVCDT+POSTDT=*STR(00000000) THEN *STR(NO_DATE);INVCDT=*STR(00000000) THEN POSTDT;INVCDT)