Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jahnavi11
Contributor II
Contributor II

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;

Labels (1)
3 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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
Contributor II
Contributor II
Author

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
Partner - Specialist III
Partner - Specialist III

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