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: 
jozisvk11
Creator
Creator

Create new variable wit descending numbering

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,

1 Reply
marcus_sommer

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