Skip to Content
0

Nested Transformation with IF condition

Sep 21, 2017 at 10:53 PM

209

avatar image

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 left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Best Answer
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
10 |10000 characters needed characters left characters exceeded

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 :-)

0

"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!

0

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

0

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

0
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
10 |10000 characters needed characters left characters exceeded

The topic author is talking exactly about sting concatenation!

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

Looks like you are loading data from text file.

Please show file sample in notepad! And show full transformation file.

P.S. also please provide your BPC and BW version and SP.

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Vadim,

I am loading the data from BW Info provider

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)
0

And please show sample cube data in RSA1

0

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)
0
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
10 |10000 characters needed characters left characters exceeded

Yep will give it a shot and see how it goes

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

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

Share
10 |10000 characters needed characters left characters exceeded