Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
If I had a table of data like the below, is it possible to have 3 column generated where by it increments by 1 each time the record in A is found?
So A can occur one or more times but B is unique.
Hope this makes sens.
Thanks
Aidan
A B C
test1 apple 1
test1 orange 2
test2 apple 1
test3 apple 1
test3 pear 2
Load *, AutoNumber(A&B, A) as ID Inline
[
A, B, C
test1, apple, 1
test1, orange, 2
test2, apple, 1
test3, apple, 1
test3, pear, 2
];
Load *, AutoNumber(A&B, A) as ID Inline
[
A, B, C
test1, apple, 1
test1, orange, 2
test2, apple, 1
test3, apple, 1
test3, pear, 2
];
The above was just a sample, I have hundreds of thousands of records, all numeric so I dont think the above is feasible, thanks for your help though
Provide some more sample data lines..
These data lines are completely random, there is thousands of possibilities
Without providing data, how can we know about them?
Rowno() works as a output to load statement try that.
Hi I have tried that already thanks but the folliwng happens to me
A, B, C
test1, apple, 1
test1, orange, 2
test2, apple, 3
test3, apple, 4
test3, pear, 5
anyone have any ideas on this?
I have tried this and for the short example it works. Maybe you can adjust it to your requirements?
TESTa:
Load * Inline [
A, B
test1, apple
test1, orange
test2, apple
test3, apple
test3, pear
];
TESTb:
NoConcatenate load
A,
B,
if (A<>Previous(A),1,null()) as C
resident TESTa;
Let vNrRows = 1;
do
while
vNrRows>0
TESTc:
noconcatenate
Load *
Resident TESTb
where
isnull(
C);
Let
vNrRows =
NoOfRows('TESTc');
drop
table TESTc;
TEST1:
NoConcatenate
load
A,
B,
if (
A=
previous(
A),
previous(
C)+1,
C)
as
C
Resident TESTb;
// where isnull(C);
drop
table TESTb;
rename
table TEST1
to TESTb;
loop;
drop
table TESTa;