Filter for a budget key

[philippsathasivam]

Hello Friends,

i have the problem that i need to filter for a budgetkey with the following conditions: it has to start with I, the length has to be 5 and the 4 other characters have to be numbers (0-9). So for example I8945, I7333, I4598 are allowed. I787, XX777, 89VVV, are not allowed. I can use a complex selection with left(BUDKEY,1)='I' and length(BUDKEY)=5. Then i built flags LET1, LET2, LET3, LET4 for each of the 4 digits like the following:

Then i filtered out the flags with 'n' that didnt match the 4 conditions with a complex selection. I did this and got the right solution. Now this seems a bit cumbersome. Does s.o know a function like CAST, ISNUMERIC, TRYCONVERT which i can use in PaPM-SQL to solve this faster? Any tips would be appreciated.

Best regards, Philipp

[zhiyitang · Accepted solution]

Hi philippsathasivam,

you might try to leverage the function LIKE_REGEXPR to simplify your formulas. For instance:

CASE WHEN LET1 LIKE_REGEXPR '[0-9]' THEN 'Y' ELSE 'N' END

I hope it would be helpful.

Best regards,

Zhiyi Tang

[zhiyitang · Accepted solution]

Further analysis on your requirement would suggest a more simplified approach with which you would only need one flag:

CASE WHEN LENGTH(BUDKEY) = 5 AND BUDKEY LIKE_REGEXPR 'I[0-9]{4}' THEN 'Y' ELSE 'N' END

Best regards,

Zhiyi

[philippsathasivam · Accepted solution]

Dear Tang,

thx for saving me again (you helped me with the depreciation/Funds transfer pricing). This works very well and way faster than my solution. You are the best.

Regards, Philipp