topic Re: Duplicating records in QlikView

Duplicating records

Thu, 13 Jun 2013 08:59:01 GMT

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!

Re: Duplicating records

Gysbert_Wassenaar — Thu, 13 Jun 2013 09:12:39 GMT

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.

Re: Duplicating records

Thu, 13 Jun 2013 09:39:44 GMT

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-1

111-2

111-3

Re: Duplicating records

Gysbert_Wassenaar — Thu, 13 Jun 2013 09:51:14 GMT

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...;

Re: Duplicating records

Thu, 13 Jun 2013 10:12:22 GMT

Thanks! thats a big help

Re: Duplicating records

hic — Thu, 13 Jun 2013 10:12:34 GMT

A while loop is cleaner:

Load *,

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

From ...

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

HIC