Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
i have this table
metric | component |
---|---|
1 | a |
1 | b |
1 | c |
2 | d |
2 | e |
3 | f |
3 | e |
4 | s |
4 | r |
4 | g |
5 | j |
and i want to make this table-
metric | component | component num |
---|---|---|
1 | a | 1 |
1 | b | 2 |
1 | c | 3 |
2 | d | 1 |
2 | e | 2 |
3 | f | 1 |
3 | e | 2 |
4 | s | 1 |
4 | r | 2 |
4 | g | 3 |
5 | j | 1 |
how should i do it?
in the script
adi
May be this
LOAD metric,
component,
AutoNumber(component, metric) as [component num],
FROM ...;
May be this?
LOAD metric, component, AutoNumber(RowNo(), metric) as [component num]
FROM [https://community.qlik.com/message/1313537]
(html, codepage is 1252, embedded labels, table is @1);
load
*,
if(metric=peek('metric'),peek('component num')+1,1) as [component num];
from YourTable
order by metric, component;
This should work.
Fact:
//Create Row ID
load *,RowNo() as Row_ID;
Load * inline
[Metric,Component
1,a
1,b
1,c
2,d
2,e
3,f
3,e
4,s
4,r
4,g
5,j];
// Create Rank
left join (Fact)
load
Row_ID,if(Metric=previous(Metric),peek(Rank)+1,1) as Rank
resident Fact;