(Partial) PDF to Alteryx then an approx. vlookup

[goutdelete]

Hi all,

I'm (very) new to Alteryx and I have a task at hand but couldn't seem to get it work even after reading through a few posts on community here and there.

I have a PDF file as attached from Equifx, ultimately I need to get the credit score to "bad rate" (probability of default) mapping on page 11. (The real Equifax PDF I got is different from this file, which I just download randomly online).

In the next step when I join with my other table (file) with each applicant's credit score information, I can then "vlookup" its corresponding bad rate as below in order to do my next assessment and analysis:

Applicant	Credit Score	Bad Rate
A	523	42.60%
B	700	3.80%
C	650	9.10%

For the moment I have trouble even running the PDF input tool (not sure if it's because I'm using trial version still so a bunch of error messages popping up).  But even if I manually cleaned up the mapping table and import into Alteryx directly, I can't seem to successfully use find and replace function (which seems to me only works successfully with string data type too?)

Any pointers will be appreciated!

[mceleavey]

Hi @goutdelete ,

I've attached a workflow that shows two working methods of loading in PDF files.

The first is using the Intelligence Suite tools which now has a native pdf reader, which you then convert to text and split to rows.

The second is using a community built tool (shout-out to @DiganP who I think is the author).

I've attached the workflow and the macro (which you will need to save to your macros folder).

Following this, you will use the join tool to join to the reference table, or the input you will have. Sorry, I can't build this for you as I don't have the data or enough information.

I hope this helps,

M.

[goutdelete]

Thanks @mceleavey !

Unfortunately I don't have Intelligence Suite at the moment and we are so far using the trial to test it out and build POC first.  We are suspecting there may also be something to do with the admin/ non admin version with certain error message we received so let me take that away. 

So ideally after Step-1 (after importing PDF and cleaning it all up) I would get the A:B column as below.

And for the second part with my applicant info table I will be able to look up the default rate (bad rate) published by Equifax.  Since it's not an exact match lookup, in excel it would be a vlookup with a "true" or "1" at the end.  I actually found an error in my original post, the answer for A Applicant the bad rate should've been 0.368 or 36.8%.  Currently we've tried "find and replace", joint.. etc, nothing had worked.  We have a few use cases like this and the range and target will keep changing, so a long nesting "IFs" isn't an ideal solution for us.

Hope this clarifies things and thanks in advance! 🙂

[mceleavey]

@goutdelete ,

This is now simply text parsing. 

When you get the Intelligence suite you can use the first method to load in you .pdf, and for now you can use the second method.

I suggest you now use the following resources to get you up to speed on text parsing:

https://help.alteryx.com/current/designer/text-columns-tool

https://help.alteryx.com/current/designer/regex-tool

You can also find some useful resources on Regex parsing here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/RegEx-Examples-12-Handy-Use-Cases/td-p... - A handy article by @brianprestidge 

https://community.alteryx.com/t5/Interactive-Lessons/Parsing-Data-with-RegEx/ta-p/441415

I hope this gets you up and running.

M.

[goutdelete]

@mceleavey Thanks very much again!

Any chance you can give me some pointers to the "approx. vlookup" part too (the second step)? I just realized in my model I have at least two other portions where I need to apply the same technique to solve the problem. 

Basically I have a value (number) to check if it falls into a range, and grab the corresponding value (number again).

It doesn't look like I can or should use find/replace (my understanding is only for strings), and I can't seem to use either joint or union either since the target is going to be a range.   The range is always changing so writing a long IF sentence isn't the best option nor best practice to me unfortunately.