Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'?
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
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
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
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