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);

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 

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

Change icon for a particular file extension

Change icon for 7z files in computer

1. First of download any image you like for icon

suitable size of image / icon in windows are:
16x16 , 32x32, 48x48, 128x128 , 256x256

note*- If image extension is other then "ico" then convert it into "ico" extension first.
you can use any online convertor "png to ico"
2.Open run > regedit

3. Look for path:

HKEY_CURRENT_USER \ Software \ Microsoft \ Windows \ CurrentVersion \ Explorer \ FileExts \ .7z \ UserChoice

4. On Right pane , note down the value data for Progid. [in this case progid not exists]

image 1

5. If the UserChoice branch does not exist, navigate to the following branch:
HKEY_CLASSES_ROOT \ .7z

6. In the right-pane, note down the (default) value data. This is the Prog ID for the file extension.

image 2

7. Navigate to the following key:
HKEY_CLASSES_ROOT \ 7z_auto_file\ DefaultIcon

Now if the DefaultIcon key is exists then change the path

If not exists Just create a new key DefaultIcon and enter the path of icon where it resides

Restart your computer so that changes can take effect.

QV20 Filter the null values of column in set analysis

I have SCOTT.EMP table with us, and want to filter out null values .
the basic requirement is to filter the data using set analysis whose MGR is null.

Dimension - MGR
expression - SUM (SAL)

Now i want to remove null's from dimension of chart (i.e. sum= 5000 where MGR is null),
i can do in various ways but i want to use using "set analysis"


expression - sum({<MGR-={'=Len(Trim(MGR))=0'} >} [SAL])