on 07-15-2015 4:38 PM
Dear All,
In my source transaction data I have following :
GL account | Amount |
---|---|
A.123 | |
A.234 | |
B.111 | |
C.123 | |
The result that I want is:
if GL account=A* then map it to 888
if GL account<>A* then map it to the 3 digits of last characters.
in the conversion file I maintain as follow :
external | internal |
---|---|
A* | 888 |
Question:
How do I maintain the conversion file to say
"if GL account<>A* then map it to the 3 digits of last characters."
Thank you in advance for your sharing.
You might not need conversion file at all.
Use this in the transformation file and you should be good.
Your_field = *IF(GL_ACCOUNT(1:1)=*STR(A) then *STR(888);*STR(XYZ))
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Gajendra,
Thank you for your prompt feedback.
but that's what I want
your "IF" formula will map all non "A" account to the fixed account XYZ. I need to map it to the last 3 characters of external account. For example if external account is B111 then the system should map it to internal=111, if external account is C123 then system should map it to the internal=123.
Furthermore, the "IF" formula in the transformation is limited to few IF only.
My real scenario requires more that 15 "IF". Sample scenario above is only simple sample to explain my question
I am not very clear about your requirement.
As per your original post, you have accounts starting with A as exception and to be assigned a constant value 888. For all other accounts, you need last 3 characters of the account. Assuming the length is constant and for all accounts not beginning with A, the formula should work. Why would you need multiple IFs. The conditions you have are - IF=A* and IF<>A* for which formula is good enough.
Kindly provide more details if I have missed something.
in my sample scenario, it is only IF=A* and IF<>A*... and IF in the transformation as per your formula will work perfectly.
in actual scenario, I have more than 15 IF conditions . Hence IF in the transformation will not work because IF in the transformation are limited (can not reach 15 IF). Please correct my understanding on this.
Apologize it my sample scenario is misleading...
Hi Ankur,
do you mean if I have A until Z then I have to maintain 26 rows?
is there any way to say like this :
for 15 conditions (let say A to O), i maintain the mapping in the conversion file.
for other conditions just take the last 3 characters?
so perhaps i can only maintain 16 rows in the conversion file???
Thank you in advance.
what's the different between "*" and "?" ?
?***=*** is used to map the internal account with last 3 characters when the external account has 4 chars
???*****=***** does it mean to map internal account with last 5 chars when the external account has 8 chars?
?***? = *** does it mean "map the internal account with second,third, fourth characters" ?
Basically * represents any number of characters and ? represents one character.
A* = *
This means that any string starting with A will be mapped to same string but A will be truncated. For eg.
A12345 will be mapped to 12345.
? means one character. For e.g.
??* = *
A12345 will be mapped to 2345. In this case initial 2 places will be truncated represented by two ??
I hope this is clear.
Hi Andy
My understanding is that your solution would only suffice for one case where GL_ACCOUNT=A* and <>A*. You will need to include similar conditions for other 14 conditions too - say GL_ACCOUNT=P* and <>P* and so on.
Had there been one condition only, you would not even need conversion file. See transformation mapping suggested above.
Okay. I will try.
Help me understand the following.
If GL_ACCOUNT=A* then result=888 else last three characters. (Your logic satisfies this)
Second condition
GL_ACCOUNT=B* then result=999 else last three characters. (I do not see first part of it happening)
And there will be 13 more conditions like this. For discussion sake say GL_ACCOUNT=C*,D*,E* and so on with each of them say need to have a fixed value like 666,777,111 etc.
Correct. That is the main concern here which we all are trying to resolve. Glad we are on the same page.
The solutions provided by me, you and Ankur will have 16 lines for 15 conditions - 15 for constant values and one condition for getting last three characters. Vadim indicated a one-line conversion statement which will be interesting to learn.
You can use very complex id transformation logic in the conversion file with javascript!
Vadim
P.S. Please describe full logic!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.