topic Re: Need logic help for populating rowno for each set in QlikView

Need logic help for populating rowno for each set

sridhar_sigired — Fri, 29 Apr 2016 04:07:38 GMT

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...

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 — Fri, 29 Apr 2016 06:08:55 GMT

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

Kushal_Chawda — Fri, 29 Apr 2016 06:16:20 GMT

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 — Fri, 29 Apr 2016 06:33:43 GMT

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 — Fri, 29 Apr 2016 06:36:36 GMT

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