Create new variable wit descending numbering

jozisvk11 — Wed, 25 Nov 2020 16:16:04 GMT

Hello, I have problem with creating new fields. My table is:

Purchase Sum of purchase Debit/Credt (Debit=1, Credit=2) Rank of the rows

M40 100€ 1 3

M40 200€ 1 2

M40 50€ 1 1

M40 300€ 2 1

M38 500€ 1 1

M38 500€ 2 1

M45 50€ 1 4

M45 70€ 1 3

M45 20€ 1 2

M45 100€ 1 1

M45 300€ 2 2

M45 60€ 2 1

........

I need create new variable - ID according to rank of the rows, but with opposite numbering (descending)

My result should be :

Purchase Sum of purchase Debit/Credt (Debit=1, Credit=2) Rank of the rows ID(opposite descending of "Rank of rows")

M40 100€ 1 3 1

M40 200€ 1 2 2

M40 50€ 1 1 1

M40 300€ 2 1 1

M38 500€ 1 1 1

M38 500€ 2 1 1

M45 50€ 1 4 1

M45 70€ 1 3 2

M45 20€ 1 2 3

M45 100€ 1 1 4

M45 300€ 2 2 1

M45 60€ 2 1 2

Can you help, how to create ID variable ?

Thank you,

Re: Create new variable wit descending numbering

marcus_sommer — Thu, 23 Jun 2016 11:28:13 GMT

I think you could Peek() or Previous() ? within a sorted resident load, maybe in this way:

load *, if(Purchase = previous(Purchase), peek('ID opposite') + 1, 1) as [ID opposite] Resident YourTable;

- Marcus