Transformation File Error

[Former Member]

Hello Experts,

I have created one transformation file for MD flat file loads.

I need to bring in ID's with the filter of another column  FIL_ACCT which has "Y".

Just keep in mind that the column which has "Y" and "N" is not mapped with Dimension properties. I just need to bring ID's from ID column which have value "Y"  in column FIL_ACCT.

Following is the scenario:

ID	FIL_ACT
ACCT1	N
ACCT1	N
ACCT1	N
ACCT1	N
ACCT2	Y
ACCT3	Y
ACCT4	N
ACCT4	N
ACCT4	N
ACCT5	N
ACCT5	N
ACCT5	N
ACCT5	N
ACCT6	Y

In above example, I just need to bring ACCT2, ACCT3, AND ACCT6.

Any expert opinion highly appreciated.

Muhammad

[former_member186338 · Accepted solution]

And another option:

In transformation file MAPPING use something like:

ID=FIL_ACCT+ID

In CONVERSION

ID=ACCTCONV.xls

Where:

External    Internal

N*              *skip

Y*              *

The simplest one !

[Former Member · Accepted solution]

Thank you so much Vadim.

I think this would work.

Muhammad

[Former Member · Accepted solution]

Vadim,

I am getting following error.

Dimension member  is an invalid member ID

[former_member186338 · Accepted solution]

In my system it's working! You have done something wrong. Please provide:

Text file sample in notepad

Transformation file

Conversion file

[Former Member · Accepted solution]

Hello Vadim,

Following is my transformation file.

Below bridges-dim_ind contains values Y and N

and bridges_r4_cd contains ID's

ID=bridges_dim_ind+bridges_r4_cd

*CONVERSION

ID=ORG_BRIDGES_IND.xls

Here is following conversion mapping.

N*	*SKIP
Y*	*

[former_member186338 · Accepted solution]

Data file in notepad please!

P.S. And full transformation file

[Former Member · Accepted solution]

Hello Vadim,

Your above logic is working fine, but there is one more issue. There are many blank rows in ID column which I need to skip at the same time.

Muhammad

[former_member186338 · Accepted solution]

It's a different question not related to original discussion.

But you can skip lines like:

N         *SKIP

Y          *SKIP

It will skip lines with empty ID.

[Former Member · Accepted solution]

Hello Vadim,

It is related to the original discussion. Please see scenario below.

ID	ACCT_FIL
ACCT1	N
ACCT1	N
ACCT1	N
ACCT1	N
ACCT2	Y
ACCT3	Y
ACCT4	N
ACCT4	N
ACCT4	N
ACCT5	N
ACCT5	N
ACCT5	N
ACCT5	N
ACCT6	Y
	N
	N
ACCT9	N
ACCT9	N
ACCT10	Y

As original discussion, we are bringing only ID's with "Y" and skipping "N". Your recommendations work fine. During my test, I deleted all the blanks and your solution works fine. But If I have blank rows there, then it is giving me the error. Basically I have three conditions.

1. Just bring Y related ID's

2. Skip N related ID's

3. Also need to skip Blank rows.

Muhammad

[Former Member · Accepted solution]

Hello Vadim,

I have used IF condition in transformation file and it is been resolved. Again thank you so much for your valuable input.

You are the best.

Muhammad

[former_member186338 · Accepted solution]

Looks like you haven't read my previous answer.

I Recommended you to have:

Y       *SKIP

N       *SKIP

N*     *SKIP

Y*       *

[Former Member · Accepted solution]

Hello Vadim,

Yes I did sir and tried your suggestions.

I will try this solution too.

Though I have solution, but will try this one too.

Again, thank you so much for your great input.

Muhammad

[former_member186338 · Accepted solution]

If you mark a discussion as assumed answered you need to post at least your solution

[Former Member · Accepted solution]

Hello Vadim,

Yes you are right and I apologize for not doing so. Please see below my solution.

ID=*IF( ID=*STR() then *STR(NO);ID+ACCT_FIL )

Following is conversion.

N*=*SKIP

Y*=*

Muhammad

[former_member186338 · Accepted solution]

In general it's the same   Just I used the conversion file to test for empty ID

[Former Member · Accepted solution]

Yes Mr Vadim,

Due to your initial logic,I was able to solve it.

Muhammad

[former_member186338]

Number of options:

- Preprocess the file to remove lines with N

- Place the column with FIL_ACCT as first column and use SKIPIF in transformation

- Use routine badi