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

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

jahnavi11 — Sat, 16 Nov 2024 07:53:25 GMT

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.

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;

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

felipedl — Mon, 30 Jul 2018 17:19:24 GMT

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:

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

jahnavi11 — Mon, 30 Jul 2018 18:57:01 GMT

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

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

felipedl — Tue, 31 Jul 2018 11:19:46 GMT

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.