topic Re: how to replace NULL value as top most value in pivot table? in QlikView

how to replace NULL value as top most value in pivot table?

Fri, 25 Apr 2014 09:41:09 GMT

Hi,

Can anyone suggest me how can I get the replaced NULL value as top most value in pivot table? please see attached. As I replaced the Null Values with 'Not Default UTD' . I would need this at the starting of the column values.

Thanks.

Re: how to replace NULL value as top most value in pivot table?

Fri, 25 Apr 2014 09:41:21 GMT

Please see attached.

Re: how to replace NULL value as top most value in pivot table?

rustyfishbones — Fri, 25 Apr 2014 09:43:35 GMT

So you may need to sort by the Expression!!

Re: how to replace NULL value as top most value in pivot table?

Fri, 25 Apr 2014 09:43:37 GMT

I would need output as in attached.

Re: how to replace NULL value as top most value in pivot table?

Fri, 25 Apr 2014 09:43:54 GMT

Easiest way in the backend create a sort order and map it to the table with that field in, then just sort by the newly created field

Re: how to replace NULL value as top most value in pivot table?

Fri, 25 Apr 2014 09:45:06 GMT

Thanks any example please?

Re: how to replace NULL value as top most value in pivot table?

Fri, 25 Apr 2014 09:45:33 GMT

Thanks. Tried but it's not working. any solution please?

Re: how to replace NULL value as top most value in pivot table?

Fri, 25 Apr 2014 09:49:37 GMT

Map1:

MAPPING LOAD * INLINE [

FieldIWantToSortPossibleValues,SortOrder

1 Month,1

2 Month,2

];

LOAD FieldIwantToSort,

ApplyMap('Map1',FieldIWantToSort,999) AS SortOrder

FROM ...

Sort via expression on chart and input =SortOrder

Re: how to replace NULL value as top most value in pivot table?

SunilChauhan — Fri, 25 Apr 2014 10:09:50 GMT

in sort expression

use

wildmatch(AgeSummaryMarch, null())

or use ' ' in place of null() in above exp

and use ascending or descendeing and see result

Re: how to replace NULL value as top most value in pivot table?

Fri, 25 Apr 2014 10:10:25 GMT

when I am executing this it is showing a error as path specified can not found

Re: how to replace NULL value as top most value in pivot table?

Fri, 25 Apr 2014 10:14:32 GMT

Tried not working.

Re: how to replace NULL value as top most value in pivot table?

SunilChauhan — Fri, 25 Apr 2014 10:17:14 GMT

hi use your field in pplace in wildmatch

there is typo

Re: how to replace NULL value as top most value in pivot table?

Fri, 25 Apr 2014 10:21:17 GMT

sorry I confused here. the field which I have is AgeSummary_March. please explain a bit more

Re: how to replace NULL value as top most value in pivot table?

SunilChauhan — Fri, 25 Apr 2014 10:25:35 GMT

use wildmatch(AgeSummary_March,Null()) in sort expression

the sort order asc or desc from dropdown available

or wildmatch(AgeSummary_March,' ') then sort order asc or dsc

hope this helps

Re: how to replace NULL value as top most value in pivot table?

Fri, 25 Apr 2014 10:26:48 GMT

It sounds like it cant find the file you are trying to load

Re: how to replace NULL value as top most value in pivot table?

Fri, 25 Apr 2014 10:44:25 GMT

Is this correct which I am using?

Map1:

MAPPING LOAD * INLINE [

AgeSummary_March,SortOrder

'',1
1 Month,2

1Month,3
2month,4
3months,5
4months,6
5months,7
6months,8
7-11months,9
12months+,10
];

LOAD AgeSummary_March,

ApplyMap('Map1',AgeSummary_March,10) AS SortOrder

FROM Map1;

Re: how to replace NULL value as top most value in pivot table?

Fri, 25 Apr 2014 10:51:40 GMT

Map1:

MAPPING LOAD * INLINE [

AgeSummary_March,SortOrder

"1 Month",2

"1Month",3
"2month",4
"3months",5
"4months",6
"5months",7
"6months",8
"27-11months",9
"12months+",10
];

Table1:

LOAD AgeSummary_March,

ApplyMap('Map1',AgeSummary_March,1) AS SortOrder

FROM ;

Re: how to replace NULL value as top most value in pivot table?

sudeepkm — Fri, 25 Apr 2014 11:08:52 GMT

Is it possible to post the sample qvw you have?

Re: how to replace NULL value as top most value in pivot table?

Fri, 25 Apr 2014 11:11:22 GMT

Thanks. The script is executing. But when I use 'SortOrder' in sort expression it is not showing 'Not Default UTD' in top. Do I need to change anything here?

Re: how to replace NULL value as top most value in pivot table?

eduardo_sommer — Fri, 25 Apr 2014 14:12:54 GMT

There is a simple way to do this. Before you load the main table, include the inline load below:

Labels:

Load * INLINE [

AgeSummary_March

Not Default UTD

1Month

1month

2months

3months

4months

5months

6months

7-11monts

12months+

];

It's importante that the name of the column, and the labels are exactly the same as in your table.

In the pivot table (order tab) choose input order. Since the inline load is executed before the load of your table, it will determine the input order.

Let me know if this worked for you.