Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andreip21
Contributor III
Contributor III

autonumber for client id x order id

Hello,

i have a huge database or customer id, order id and value of order.

i want to add a new column (the 4th one), like a flag, which counts the number of the order for each customer.

for ex:

if customer A has 2 orders (#145 and #267) and customer B has 3 orders (#189 #211 and #214), the final table will be this one:

customerorder idflag order
A1451
A2672
B2112
B2143
B1891

this will work also as a dimension, if selecting 1, i will have all the first order for all customers.

 

thanks a lot,

Andrei

 

5 Replies
mikaelsc
Specialist
Specialist

sort your table per customer, and in descending order of orders (lol) 

then something like

FinalTable:

load

if(previous(customerID)<>customerID,1,peek(NumberOfOrders)+1) as NumberOfOrders,

*

resident YourSortedTable;

 

mikaelsc
Specialist
Specialist

ascending order of orders is fine 

NitinK7
Specialist
Specialist

hi,

Try the following

use autonumber

ABC:
LOAD customer,
[order id],
value,
AutoNumber([order id],customer) as flag
FROM
data.xlsx
(ooxml, embedded labels, table is Sheet1);

or 

AutoNumber(RowNo(),customer) as flag

Capture.JPG

Vegar
MVP
MVP

I notice you, @andreip21 , got two working solutions to your problem (if I and my two fellow community members understood your problem correctly). 

Even though both suggestions can give you your desired output i would assume that @mikaelsc  will probably perform much faster than the slick autonumber solution by @NitinK7 .

So my suggestion would be to try the peek()-solution first, but it is crucial that you get the sort order right before you apply any of the suggestions. 

-Vegar

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Brett_Bleess
Support (Former)
Support (Former)

Lastly, if one of the posts got you what you needed, or they both worked, please be sure to return to your thread to close things out by using the Accept as Solution button on the post(s) that helped you with things, as this gives them credit for the help and lets the other Members know what worked, so please consider closing things out for us, or leave a new post if you have further questions.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.