Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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