Nested Transformation with IF condition

[former_member314654]

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?

[former_member186338 · Accepted solution]

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?

[former_member314654 · Accepted solution]

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 🙂

[former_member186338 · Accepted solution]

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

[former_member314654 · Accepted solution]

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

[former_member186338 · Accepted solution]

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

[former_member186338]

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

[lucas_costa3]

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)

[former_member314654]

Yep will give it a shot and see how it goes

[former_member186338]

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.

[former_member314654]

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

[former_member186338]

And please show sample cube data in RSA1

[former_member314654]

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-

[lucas_costa3]

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.

[former_member186338]

The topic author is talking exactly about sting concatenation!