Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
For example, the raw data looks like this,
LOAD * INLINE [
F1, F2 Date
A, 100 1/8
B, 90 1/8
C, 150 2/8
D, 67 2/8
E, 69 1/8
F, 99 3/8
G, 12 3/8
H, 9 2/8
];
I want to produce something similar like below
LOAD * INLINE [
F1, F2 Date Rank
A, 100 1/8 1
B, 90 1/8 2
C, 150 2/8 1
D, 67 2/8 2
E, 69 1/8 3
F, 99 3/8 1
G, 12 3/8 2
H, 9 2/8 3
];
I dont know how to add the date condition on below Code
if([F2] = peek([F2]), peek(Rank),RecNo()) as Rank
Thank you in advance everyone
stalwar1 swuehl Qlik Sense App Development & Usage Qlik Sense Forums
May be this
Table:
LOAD * INLINE [
F1, F2, Date
A, 100, 1/8
B, 90, 1/8
C, 150, 2/8
D, 67, 2/8
E, 69, 1/8
F, 99, 3/8
G, 12, 3/8
H, 9, 2/8
];
FinalTable:
LOAD *,
If(Date = Previous(Date), RangeSum(Peek('Rank'), 1), 1) as Rank
Resident Table
Order By Date, F2 desc;
DROP Table Table;
if([F2] = peek([F2]) and [Date]=peek([Date]), peek(Rank),RecNo()) as Rank
Hi Mr. David,
First of all, thank you for responding to my question. I'm really appreciate tho
I've tried your code but it still sort overall. Do you have any idea why?
I want the 11th rank to reset into 1st rank
Thank you in advance!
Muhammad,
You could try usinng the order by clause:
SAMPLE_DATA:
LOAD * INLINE [
F1, F2, Date
A, 100, 1/8
B, 90, 1/8
C, 150, 2/8
D, 67, 2/8
E, 69, 1/8
F, 99, 3/8
G, 12, 3/8
H, 9, 2/8
];
FINAL_TABLE:
LOAD
*,
if([F2] = peek([F2]), peek(Rank),RecNo()) as Rank
Resident SAMPLE_DATA Order By F2 Desc;
Drop Table SAMPLE_DATA;
Result:
May be this
Table:
LOAD * INLINE [
F1, F2, Date
A, 100, 1/8
B, 90, 1/8
C, 150, 2/8
D, 67, 2/8
E, 69, 1/8
F, 99, 3/8
G, 12, 3/8
H, 9, 2/8
];
FinalTable:
LOAD *,
If(Date = Previous(Date), RangeSum(Peek('Rank'), 1), 1) as Rank
Resident Table
Order By Date, F2 desc;
DROP Table Table;
You can also use Autonumber():
T_DATA:
LOAD * INLINE
[
F1, F2 Date
A, 100 1/8
B, 90 1/8
...
];
DATA:
LOAD F1,
F2,
Date,
AutoNumber(Date, F1&F2) as Rank
Resident T_DATA
Order By F1, F2, Date DESC;
DROP TABLE T_DATA;
Thank you so much!!
Great idea!
Thank you!
Thank you for responding!
But, You're missing one of my requirement, which is it should be filtered by F2 and Date.