Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Creator III
Creator III

Re: autonumber for client id x order id

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;

 

Highlighted
Creator III
Creator III

Re: autonumber for client id x order id

ascending order of orders is fine 

Highlighted
Creator II
Creator II

Re: autonumber for client id x order id

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

Highlighted
Partner
Partner

Re: autonumber for client id x order id

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
Highlighted
Digital Support
Digital Support

Re: autonumber for client id x order id

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.