topic Re: help with scritp in QlikView

help with scritp

adiarnon — Tue, 25 Jul 2017 16:59:32 GMT

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

Re: help with scritp

sunny_talwar — Tue, 25 Jul 2017 17:04:42 GMT

May be this

LOAD metric,

component,

AutoNumber(component, metric) as [component num],

FROM ...;

Re: help with scritp

Anil_Babu_Samineni — Tue, 25 Jul 2017 17:04:42 GMT

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);

Re: help with scritp

m_woolf — Tue, 25 Jul 2017 17:08:21 GMT

load

if(metric=peek('metric'),peek('component num')+1,1) as [component num];

from YourTable

order by metric, component;

Re: help with scritp

manas_bn — Tue, 25 Jul 2017 17:13:09 GMT

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;