topic Re: Sorting Nulls at last row in QlikView

Sorting Nulls at last row

nadkalyan — Wed, 15 Aug 2012 18:47:57 GMT

Hi , I have a straight table with a dimension and several Expressions. I did sort Dimension By Text A-->Z . It was working fine until Null Values dont come up. Null Value for dimension is coming on first row. How can i show Null Value at end(last row) still keeping my original sort for other dimension values A-->Z.

Re: Sorting Nulls at last row

manojkvrajan — Wed, 15 Aug 2012 20:47:33 GMT

Simplest solution is to introduce a flag field for this null value and use the expression sort in descending/ascending. However, you can hide the column. I understand it is little gruelling work however, this is the shortest workaround to meet the requirement.

Re: Sorting Nulls at last row

manojkvrajan — Wed, 15 Aug 2012 21:00:17 GMT

Please upload a sample qvw and see if I can be of any help.

Re: Sorting Nulls at last row

nadkalyan — Thu, 16 Aug 2012 15:32:46 GMT

Manoj,

Attached is the sample QVW file. I tried using Expression sort , still same issue. Any assistance will be highly appreciated.

Thanks

Kalyan

Re: Sorting Nulls at last row

manojkvrajan — Thu, 16 Aug 2012 16:17:42 GMT

Kalyan, I dint make any change to your qvw document. However, I just handled the script to address the null issue. Please use the IF (IsNull(Data)=-1 OR Data = '',null(),Data) AS Data in your extraction script if your data is small. Otherwise Resident load will also be an option to address this requirement. Attached is the sample. I hope it helps.

Re: Sorting Nulls at last row

Anonymous — Wed, 07 Mar 2018 16:34:43 GMT

The original qvw appears to have been taken down, but if the temporary table in the Sort Null_Solution is the same, it looks like it's the blank value that gets sorted to the top row, not a null value. In that case the solution is as simple as the sort expression "If(Data = '',1,0) ." Anything greater than "1" can be put in its place in the If statement.

I've had an issue with nulls that refused to move the bottom row. I realized that the chart type was accidentally selected as a Pivot Table rather than Straight Table. The sort expression above won't work in a pivot table.

I hope that can help someone.