How to clean the imported data with #, $, () and missing cell at load editor?

[nezuko_kamado]

Because of the characters  like #, $,  and parenthesis, even I changed the format as numeric at the load editor-  num(num#( Bill , '#.###', ',' , '.')) as Bill-,  Qlik sense still reads it as text.

Also any rows with non-numeric values, I want to switch them as NA

How can I clean data at the load editor and get the output like 'Cleaned'?

[vchuprina]

Hi,

Looks like in your source file you have data in money format, try use money# instead of num#, of course you need to modify format pattern accordingly to your data

https://help.qlik.com/en-US/sense-kubernetes/Subsystems/Hub/Content/Sense_Hub/Scripting/Interpretati...

Also please note that you can add pattern for positive and negative values just separate them by ;

'$#,##0.00;($#,##0.00)'

num(money#( Bill , '$#,##0.00;($#,##0.00)')

Please check this, if it doesn’t work I will help you tomorrow once I have access to my laptop

Regards,

Vitalii

[vchuprina · Accepted solution]

Hi,

Here is a final solution:

alt(num(Money#(Bill, '$#,##0.00;($#,##0.00)')), 'NA') 

Script

DATA:
LOAD
Bill,
alt(num(Money#(Bill, '$#,##0.00;($#,##0.00)')), 'NA') AS BillNew
;

//Test Data:
LOAD * Inline[
Bill
#N/A
$10,000.00
$20,000.00
$30,000.00
($1,566.06)
($17,189.30)
]
(delimiter is ';')
;

Result:

Regards,

Vitalii