topic Re: Flagging numerical data incidentally loaded with text in QlikView

Flagging numerical data incidentally loaded with text

jason_nicholas — Mon, 23 Oct 2017 16:03:46 GMT

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!

Re: Flagging numerical data incidentally loaded with text

vishsaggi — Mon, 23 Oct 2017 16:07:31 GMT

Use like

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

OR creating a flag

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

Re: Flagging numerical data incidentally loaded with text

swuehl — Mon, 23 Oct 2017 16:37:40 GMT

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

Re: Flagging numerical data incidentally loaded with text

jason_nicholas — Mon, 23 Oct 2017 17:17:09 GMT

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.

Re: Flagging numerical data incidentally loaded with text

jason_nicholas — Mon, 23 Oct 2017 17:17:52 GMT

Thank you. SubField is what I needed.