How to use ORDER BY (columnA, columnB) in a expression

[Clarivando Belizario Junior]

TABLE 1:

 

 

 

 

Column_1

Column_2

 

 

A

c

 

 

 

B

b

 

 

A

a

 

 

D

a

 

 

A

b

 

 

B

a

 

 

A

d

 

 

C

h

 

 

C

f

 

 

D

g

 

 

 

I need to get a result like TABLE 2.

TABLE 2:

 

 

 

 

Column_1

Column_2

Previous(Column_2)

 

 

A

a

-

 

 

A

b

a

 

 

A

c

b

 

 

A

d

c

 

 

B

a

d

 

 

B

b

a

 

 

C

f

b

 

 

C

h

f

 

 

D

a

h

 

 

D

g

a

 

 

 

I really need the column "Previous (Column_2)". I need to know what the previous value is for each row (Column_2) in table 2 because I need to compare both of them.

[Khushboo Rabadia]

You can get previous of column2 in the same table using calculated columns instead of creating sorted table2.

 

2. Data > Add calculated column...

Column name: Rank

Expression: DenseRank(RowId(),[Column_1])

3. Data > Add calculated column...

Column name: PrevCol2

Expression: First([Column_2]) OVER (Intersect(Previous([Rank]),[Column_1]))

Rank column will assign serial numbers for each column1 section

Then you can extract previous of col2 based on previous rank value.

[Clarivando Belizario Junior]

But this way it's getting the "previous" after grouping Column_1. I'd like to get the "previous" after grouping and "sorting" (or simulating sorting) columns 1 and 2.

[Khushboo Rabadia]

You can concatenate column1 and column2 to form a group

Concatenate([Column_1],[Column_2])Then rank the values of above column in ascending order

DenseRank([newcol],"asc")Then retrieve the previous value of column2 based on above ranking

First([Column_2]) OVER (Intersect(Previous([newrank])))This way you will get the previous values as shown above.

------------------------------------

You can also use simple data function with TERR script like below:

op

[Clarivando Belizario Junior]

Thanks! This is the solution!