Duplicating records

[#1]

Hi,

I have the following table inputed:

ID	Name	Place	Number of records	date	Amount
111	QWE	USA	3	01/05, 02/05, 03/05	24
222	RQW	UK	1	09/09	57
333	GFD	FRA	1	08/07	23
444	FGG	KWD	1	04/02	178
555	MJH	GER	2	06/08, 24/09	34
666	FFD	ITA	1	07/04	23

I need to dulplicate certain rows depending on what the "number of records" column says and also split the dates so that there is only 1 in each field.

The end table I expect to see is the following:

ID	Name	Place	Number of Records	date	amount
111-1	QWE	USA	1	01/05	24
111-2	QWE	USA	1	02/05	24
111-3	QWE	USA	1	03/05	24
222	RQW	UK	1	09/09	57
333	GFD	FRA	1	08/07	23
444	FGG	KWD	1	04/02	178
555-1	MJH	GER	1	06/08	34
555-2	MJH	GER	1	24/09	34
666	FFD	ITA	1	07/04	23

Is it possible to do this in QlikView?

thanks!

[Gysbert_Wassenaar]

Yes, you use the subfield function for that.

load ID, Name, Place, 1 as [Number of Records], subfield(date,',') as date, amount

from ...somewhere...;

The subfield function will create a record for every value in the comma separated list in the date field.

[#3]

that is almost perfect thanks!

one question though:

I need the ID field to be unique, how can I do this?

i.e. I want to go from

111

111

111

TO

111-1

111-2

111-3

[Gysbert_Wassenaar · Accepted solution]

Try this:

load ID & '-' & autonumber(recno(),ID) as ID, Name, Place,[Number of Records], date, amount;

load ID, Name, Place, 1 as [Number of Records], subfield(date,',') as date, amount

from ...somewhere...;

[#5]

Thanks! thats a big help

[hic]

A while loop is cleaner:

Load *,

     Subfield(date,',',IterNo()) as Separate Date

     From ...

     While IterNo() <= [Number of records];

HIC