Modifying Currency Field / String question?

[caseyjohnson]

New to Qlik Sense and struggling a bit to understand how to implement scripting. Not sure if it's best practice to modify data in the LOAD, Through the use of variables, or through formulas in the APP???? I'm also really struggling to find examples used in context vs. just finding the script (because it's not clear to me where it goes yet?).

Regardless, this is what I'm trying to do. The "Est. Retail Value" field below outputs a currency string? (Ex. $450 USD). I'm trying to strip out the "USD" and convert it to a number to be used as a measure. What's the best way to do this? Should I do it in the LOAD editor, or somewhere else? If so, what does that look like?

I tried the "Replace" option, but that's not working for me.

LOAD

    "Est. Retail Value",

         Replace('Est. Retail Value','USD','') as "New Retail Value",

    "Item Number",

    Location,

FROM [lib://Webfile]

(txt, utf8, embedded labels, delimiter is ',', msq);

[sunny_talwar]

May be like this:

LOAD

    "Est. Retail Value",

        Money#(Trim(Replace('Est. Retail Value','USD','')), '$##') as "New Retail Value",

    "Item Number",

    Location,

FROM [lib://Webfile]

(txt, utf8, embedded labels, delimiter is ',', msq);

[caseyjohnson]

Sunny T, thank you for your assistance. That did not work on for me (I'm doing the development on the desktop version of Qlik Sense). Do you have any other ideas on this?

[sunny_talwar]

Would you be able to share your raw data file to try this out?

[caseyjohnson]

Sunny,

As I'm getting better at Qlik, I'm trying to implement your suggestions from earlier. I still couldn't get the above recommendation to work, however I found another alternative which worked and thought I should share.

I used this in a measure which worked:

Avg(left( [Est. Retail Value] , len([Est. Retail Value])-4))