Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
I was just reading a post this evening and i wanted to solve it, but i couldnt cause i do not have much experience within this and i think its hard to find good examples.
IS there anyone with solid experience in when to use what when you want to number rows? I read HIC blog about Counters but its more explaination than examples.
Let me show some examples i would like to be able to solve:
Customer | Date | Amount | HowToGetThis? |
1 | 02-01-2013 | 50 | 1 |
1 | 02-01-2013 | 50 | 2 |
2 | 03-01-2013 | 20 | 1 |
3 | 03-01-2013 | 34 | 1 |
4 | 03-01-2013 | 12 | 1 |
4 | 04-01-2013 | 23 | 2 |
4 | 04-01-2013 | 24 | 3 |
5 | 04-01-2013 | 24 | 1 |
Customer | Date | Amount | HowToGetThis? |
1 | 02-01-2013 | 50 | 1 |
1 | 02-01-2013 | 50 | 1 |
2 | 03-01-2013 | 20 | 2 |
3 | 03-01-2013 | 34 | 3 |
4 | 03-01-2013 | 12 | 4 |
4 | 04-01-2013 | 23 | 4 |
4 | 04-01-2013 | 24 | 4 |
5 | 04-01-2013 | 24 | 5 |
Customer | Date | Amount | HowToGetThis? |
1 | 02-01-2013 | 50 | 1 |
1 | 02-01-2013 | 50 | 1 |
2 | 03-01-2013 | 20 | 3 |
3 | 03-01-2013 | 34 | 4 |
4 | 03-01-2013 | 12 | 5 |
4 | 04-01-2013 | 23 | 5 |
4 | 04-01-2013 | 24 | 5 |
5 | 04-01-2013 | 24 | 8 |
If it possible??
An unoptimized example model will make things much clearer!
Happy code-cracking.
Peter
Yes, that's possible. This may not be the best solution but it works.
When loading your ordered table (first Customer, then Date) use the previous() function to check the last record read and compare the Customer numbers. If they are equal, define [HowToGetHis?] with a value of the previous [HowToGetThis?] + 1, otherwise its value will be one.
Something like:
LOAD Customer, Date, Amount,
if (previous(Customer) = Customer, previous([HowToGetThis?])+1, 1) AS [HowToGetThis?]
FROM/RESIDENT/WHATEVER
The second example isn't entirely clear to me... Are you duplicating the Customer Nr? Or do you want to continue counting whenever the Customer number changes? In the latter case, use the first example but substitute previous([HowToGetThis?]), previous([HowToGetThis?])+1 for the True/False clauses.
For the third example you'll need the RowNo() value that is used whenever the Customer number changes. For all other rows with an identical Customer number, use previous() again.
I don't have a QlikView version handy right now, so I'm unable to provide you with a model. Sorry.
Good luck,
Peter
An unoptimized example model will make things much clearer!
Happy code-cracking.
Peter
Fantastic - Thanks for taking your time.
Happy christmas