topic Re: Autogenerate question in QlikView

Autogenerate question

cbushey1 — Tue, 25 Nov 2014 18:48:23 GMT

I think I need to use autogenerate but I am not sure how to get this to work.

I have a file that has multiple iterations of the same ID (one per line). This ID relates to multiple transactions, hence the different rows.

I need to be able to number each row per ID.

Example

ID Date Cost #

A 11/25/14 50.00 1

B 11/20/14 99.00 1

B 11/21/14 3.55 2

C 11/25/14 6.52 1

The '#' above should always start at 1 and count the number of times my ID number appears.

Thoughts?

Re: Autogenerate question

simenkg — Tue, 25 Nov 2014 19:00:54 GMT

Temp:

Load * from table.qvd (qvd);

Data:

load *,

if(Peek(ID)=ID,rangesum(Peek(#),1), 1) as #

resident Temp

Order by ID asc;

Drop table Temp;

Re: Autogenerate question

maxgro — Tue, 25 Nov 2014 19:01:50 GMT

load * inline [

ID , Date , Cost , #

A , 11/25/14 , 50.00, 1

B , 11/20/14 , 99.00, 1

B , 11/21/14 , 3.55 , 2

C , 11/25/14 , 6.52 , 1

];

NoConcatenate

load ID, Date, Cost, if(peek(ID)<>ID, 1, peek(counter) +1) as counter resident a order by ID, Date;

drop table a;

Re: Autogenerate question

MarcoWedel — Tue, 25 Nov 2014 19:26:41 GMT

Load ID, Date, Cost,

Autonumber(RecNo(), ID) as counter

Form source

Re: Re: Autogenerate question

MarcoWedel — Tue, 25 Nov 2014 21:34:56 GMT

like this:

LOAD *,
    AutoNumber(RecNo(), ID) as #
INLINE [
    ID, Date, Cost
    A, 11/25/14, 50.00
    B, 11/20/14, 99.00
    B, 11/21/14, 3.55
    C, 11/25/14, 6.52
];

hope this helps

regards

Marco

Re: Autogenerate question

maxgro — Tue, 25 Nov 2014 21:37:11 GMT

it seems the best solution, no table to drop, just one statement

Re: Autogenerate question

MarcoWedel — Tue, 25 Nov 2014 21:52:35 GMT

thank you

Re: Autogenerate question

cbushey1 — Wed, 26 Nov 2014 13:26:41 GMT

Thanks everyone for your quick responses and assistance!