Need logic help for populating rowno for each set

[sridhar_sigired]

Hi,

I have a CallID, SegID, Store information in tables and below fields are derived in SQL.

[Order] = ROW_NUMBER() OVER (PARTITION BY CallID ORDER BY SegID)

[StoreOrder] = ROW_NUMBER() OVER (PARTITION BY CallID,Store ORDER BY SegID)

Below is the sample data...

CallID	SegID	Store	[Order]	[StoreOrder]
1	1	A	1	1
1	2	A	2	2
1	3	B	3	1
1	4	B	4	2
1	5	C	5	1
2	6	A	1	1
2	7	A	2	2
2	8	A	3	3
2	9	B	4	1
3	10	A	1	1
3	11	B	2	1
3	12	B	3	2
4	13	B	4	3
4	14	C	5	1
4	15	C	6	2

Can anyone help how can we derive these two fields in QlikView?

Do we need to use Rowno() and peek function? or...

Thank you.

[sunny_talwar]

Would you be able to share how the expected output for this sample data is going to look like?

[Kushal_Chawda]

Data:

LOAD CallID,

           SegID,

          Store

FROM Table;

New:

noconcatenate

LOAD *,

           if(rowno()=1 or CallID<>previous(CallID),1,peek('Order')+1) as Order,

          if(rowno()=1 or Store<>previous(Store),1,peek('StoreOrder')+1) as StoreOrder

Resident Data

order by CallID,SegID;

Drop table Data;

[sridhar_sigired]

Hi Sunny,

I have below 3 columns in my app.

CallID,

SegID,

Store

By using those 3, i need to derive Order, StoreOrder... Unfortunately i cannot able to share the app due to sensitive data...

Thanks.

[sunny_talwar]

Not asking you to share your app, just asking how are those two new columns going to look like for the data you provided above