Saturday, February 28, 2015

alphanumeric sort in postgres

alphanumeric sort in postgres

one day i have identify that postgres is behaving abnormally while sorting of alphanumeric column. I googled it alot and reach at decision that postgres not able to do natural sort of mix type words.
generally it give sort order like 1,1a, 10 , 2a, 2b, 30

as per natural sort it should be like 1, 1a, 2a, 2b, 10, 30

i tried with different datatype but behaviour remain same.

the solution is create a function, which will return natural sort

create table test_sort (column1 varchar);
insert into test_sort valuepad_numberss ('032');
insert into test_sort valuepad_numberss ('2a');
insert into test_sort valuepad_numberss ('01');
insert into test_sort valuepad_numberss ('1b');
insert into test_sort valuepad_numberss ('1c');
insert into test_sort valuepad_numberss ('1d');
insert into test_sort valuepad_numberss ('2');
insert into test_sort valuepad_numberss ('32t');
insert into test_sort valuepad_numberss ('4');
insert into test_sort valuepad_numberss ('28k');
insert into test_sort valuepad_numberss ('001');
insert into test_sort valuepad_numberss ('1a');
insert into test_sort valuepad_numberss ('40');
insert into test_sort valuepad_numberss ('1');

select * from test_sort order by 1
\


CREATE FUNCTION pad_numbers(text) RETURNS text AS $$
  SELECT regexp_replace(regexp_replace(regexp_replace(regexp_replace($1,
    E'(^|\\D)(\\d{1,3}($|\\D))', E'\\1000\\2', 'g'),
      E'(^|\\D)(\\d{4,6}($|\\D))', E'\\1000\\2', 'g'),
        E'(^|\\D)(\\d{7}($|\\D))', E'\\100\\2', 'g'),
          E'(^|\\D)(\\d{8}($|\\D))', E'\\10\\2', 'g');
$$ LANGUAGE SQL;


select * from test_sort order by pad_numbers(column1);

Friday, February 13, 2015

QV22 Script error code in Qlikview

Scripterror code are generally used to handle script error i.e. error handling in qlikview.

This variable will be reset to 0 after each successfully executed script statement. If an error occurs it will be set to an internal QlikView error code. Error codes are dual values with a numeric and a text component.
 
  • No error (Script Error =0)
  • General error (Script Error =1)
  • Syntax error (Script Error =2)
  • General ODBC error (Script Error =3)
  • General OLE DB error (Script Error =4)
  • General custom database error (Script Error =5)
  • General XML error (Script Error =6)
  • General HTML error (Script Error =7)
  • File not found (Script Error =8)
  • Database not found (Script Error =9)
  • Table not found (Script Error =10)
  • Field not found (Script Error =11)
  • File has wrong format (Script Error =12)
  • BIFF error (Script Error =13)
  • BIFF error encrypted (Script Error =14)
  • BIFF error unsupported version (Script Error =15)
  • Semantic error (Script Error =16)

Documentation
http://community.qlik.com/docs/DOC-5342

Example:
Check column existence in table 

Thursday, February 12, 2015

QV21 Check column existence in table

If you need to check the column exists in table ( qlikview table , resident table or any other type of load), use below code to check column existence. 


tab1:
load * inline [
sno, deleted
1,y
2,y
3,n
4,n
];

ErrorMode=0;          // continue running on errors
LOAD
  deleted,
  rowno() as row
  RESIDENT tab1: where row=1;
IF ScritpError=11 THEN
  // field doesnt exist
  TRACE field 'deleted' does not exist ;
ELSE
  // field exists
  TRACE field 'deleted'  exist;
ENDIF
ErrorMode=1;          // restore deafult error mode


or

ErrorMode=0;          // continue running on errors
LOAD
  deleted,
  rowno() as row
  RESIDENT tab1: where row=1;
IF ScriptErrorCount >= 1 THEN
  // field doesnt exist
  TRACE field 'deleted' does not exist ;
ELSE
  // field exists
  TRACE field 'deleted'  exist;
ENDIF
ErrorMode=1;          // restore deafult error mode
web stats