Assign incremental values based on ID

Mon, 09 Nov 2015 10:00:10 GMT

Hi,

I have got the following script :

[Product Category]:
   LOAD

ID                                                                                      as        ID
,If(ID <> Previous(ID), category_name, '') as    category_name
,If(ID <>Previous(ID), Order, Order+1)    as    Order

,[Product Category]                                                    as         Value

;
LOAD Distinct
'$(vOrder)'                    as Order
,'$(vcategory_name)'           as category_name
, ID
, [Product Category]           as [Product Category]
Resident
TmpProducts

Order By
ID

,[Product Category];

And that produces the following table, ex:

Product Category	Order	Value
Clothes	70	Shirt
	71	Jeans
	71	Long Sleeve shit
	71	Short
	71	Skirt
	71	Shoes
	71	Babies Shoes

But I am trying to achieve this:

Product Category	Order	Value
Clothes	70	Shirt
	72	Jeans
	73	Long Sleeve shit
	74	Short
	75	Skirt
	76	Shoes
	77	Babies Shoes

For each ID, the order should start at 70 (already in variable) and increment until it reaches the number of values by that ID.

Could you help me please achieve this goal. I was think of Loop, but I want to use it as last resource because of performance.

Thank you very much.

Re: Assign incremental values based on ID

Gysbert_Wassenaar — Mon, 09 Nov 2015 10:24:14 GMT

Try this:

,If(ID <>Previous(ID), Order, peek(Order)+1) as Order

Re: Assign incremental values based on ID

Mon, 09 Nov 2015 10:40:51 GMT

Thanks for your help.... You were a time saver (:

topic Assign incremental values based on ID in QlikView

Assign incremental values based on ID

Re: Assign incremental values based on ID

Re: Assign incremental values based on ID