0

# Nested Transformation with IF condition

Sep 21, 2017 at 10:53 PM

209

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?

Vadim Kalinin Sep 22, 2017 at 06:17 PM
0

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?

Show 4 Share

let me test the transformation. I know No_date is a bad idea, my idea is to get the data in for now

I might apply another logic for that data set once i walk through the business

well, coming to the conversion 12 entries per year, I will leave it at that for now :-)

Ellora Dobbala

"I will leave it at that for now" - still bad idea, even for now! use some unused month in the past and remove this member!

No harm in assigning it to a unused member. Just curious why and how do you think it will affect the application? or Time dimension?

Trying to understand If I am missing a bigger picture

Ellora Dobbala

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

Lucas Costa Sep 22, 2017 at 02:08 AM
0

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.

Show 1 Share

The topic author is talking exactly about sting concatenation!

Vadim Kalinin Sep 22, 2017 at 06:01 AM
0

Show 3 Share

My sample code above shows TIME however that was reference my actual field is D_Time

BPC NW 10.1, 801 SP 10

BW 7.4, SP 15

scn8.png (13.1 kB)
Ellora Dobbala

And please show sample cube data in RSA1

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)
Lucas Costa Sep 22, 2017 at 08:52 AM
0

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)

Show 1 Share

Yep will give it a shot and see how it goes

Vadim Kalinin Sep 22, 2017 at 03:32 PM
0

When importing use external option to remove leading zero's from ID's.

Share