Flagging numerical data incidentally loaded with text

[jason_nicholas]

I am looking for a way to extract numerical data only from a field which may have numerical and text data at the same time. I would like to do this during the initial load, if possible.

I have a source data field which provides a dollar value. However, if a specific field is used in the source application, a text string is appended to this numerical data. If my dollar value is $2000, and someone adds a discount percentage in a field designated for that data, the string comes out as "$2000 with 30% discount". When this happens, it is not read as numerical data and the calculation is impacted.

What I would like to do is load only numerical data from the field, and disregard any potential text. Is there a simple function I can use in my load script to accomplish this?

Alternatively, is there a way to tell QV to expect numerical data only, and flag if the data in that field doesn't conform? Thanks for the advice!

[vishsaggi]

Use like

IF(ISNum(YourfieldName) = -1, YourFieldName) AS YourFieldName

OR creating a flag

IF(ISNum(YourfieldName) = -1, 1, 0) AS IsNumFlag

[swuehl · Accepted solution]

If your dollar amounts are always at the beginning of the field value and the potential text starts separated by a space, you can do it like

LOAD

     Subfield(FIELD, ' ', 1) as FIELD,

     ....

FROM ....;

If your numbers are not automatically interpreted correctly, you can set a format string for your money amounts at the beginning of the script and use Money#() function to interpret:

LOAD

     Money#(Subfield(FIELD, ' ', 1)) as FIELD,

     ....

FROM ....;

Money# ‒ QlikView‌

SubField ‒ QlikView

[jason_nicholas]

I think this would eliminate the entire value if it was a combination of numerical and text data. Is that correct?

I wouldn't want to lose the numerical data included in a value that fails this test.

[jason_nicholas]

Thank you. SubField is what I needed.