Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jahnavi11
New Contributor

Sorting based on Dual() in cross tab when duplicate records exist.

In the below data set, when put in a cross tab in Qliksense and sorted on profile ID by expression priority below is the result . It does not consider the sorting unless selected on a customer.

If I use Dual function on Profile, in the below table , it modifies the value for profile.

Is there any way I can sort this data set, by customer and by profile id sorted by priority.

Cross tab.PNG

Dataset:

TEMP1:


LOAD



* inline [

customer|profile|priority

14368|41214|6

14368|9055|1

14368|41214|3

14109|41214|6

14109|41480|1

14109|41370|2

13339|9055|7

4633|19018|7

] (delimiter is '|');


NoConcatenate


[DUAL]:


LOAD

customer,

DUAL(TEXT(profile),NUM(priority)) as profile,

priority


RESIDENT TEMP1;



NoConcatenate


[ORIGINAL]:


LOAD

customer AS customer_orig,

profile as profile_orig,

priority as priority_orig


RESIDENT TEMP1;


DROP TABLE TEMP1;

3 Replies
felipedl
Valued Contributor III

Re: Sorting based on Dual() in cross tab when duplicate records exist.

Dual can sometimes be a little tricky.

Instead of doing the conversion of the number to text inside dual, do something like this:

EMP1:

load

*,

// Dual made from a text field instead of the conversion of number to text directly

dual(_txtProfile,profile) as xxxx;

LOAD customer,

profile,

text(profile) as _txtProfile,

priority;

LOAD * inline [

customer|profile|priority

14368|41214|6

14368|9055|1

14368|41214|3

14109|41214|6

14109|41480|1

14109|41370|2

13339|9055|7

4633|19018|7

] (delimiter is '|');

This gets me the right values, like below:

sample.png

jahnavi11
New Contributor

Re: Sorting based on Dual() in cross tab when duplicate records exist.

Thank you for your response Felip, but the sorting does not work if priority column in placed after profile column in the mentioned solution.

felipedl
Valued Contributor III

Re: Sorting based on Dual() in cross tab when duplicate records exist.

For it to be sorted by priority first, you need to change the dimension to the first level of the pivot table, since it does for the n dimension and goes down to n-1 dimension till the last one.

sample.png

Community Browser