topic Merging Rows.....in different columns in QlikView

Merging Rows.....in different columns

Fri, 31 Jan 2014 04:32:19 GMT

Part No	Creation Date	ETD
2010	20-12-2013	21-12-2013
2010	21-12-2013	22-12-2013
2011	22-12-2013	22-12-2013
2011	23-12-2013	24-12-2013
2010	24-12-2013	25-12-2013
2010	25-12-2013	25-12-2013

The above is data

Expected result in qlikview:-

Note:-

The First ETD date for a particular part no should be considered as ETD1,

The Second ETD date for a particular part no should be considered as ETD2, and so on.

Re: Merging Rows.....in different columns

sbaldwin — Tue, 11 Feb 2014 12:34:28 GMT

Hi what you are trying to do is essentially the reverse of a cross table, below is a script which does this for your example few records, hope this helps.

Thanks

Steve

temp:

LOAD

part_no,

etd,

rowno() as rk

INLINE [

part_no, creation_date, etd

2010, 20-12-2013, 21-12-2013

2010, 21-12-2013, 22-12-2013

2011, 22-12-2013, 22-12-2013

2011, 23-12-2013, 24-12-2013

2010, 24-12-2013, 25-12-2013

2010, 25-12-2013, 25-12-2013

] ;

temp2:

noconcatenate load part_no,etd,'ETD'&AutoNumber(rk,part_no) as cols

Resident temp

order by part_no,etd asc;

drop table temp;

temp:

load distinct cols Resident temp2;

let v_newcols = NoOfRows('temp');

temp3:

load distinct part_no Resident temp2;

for i = 1 to $(v_newcols)

set v_newcol = ETD$(i);

join (temp3)

load part_no,etd as $(v_newcol) Resident temp2 where cols = '$(v_newcol)';

next i;

drop table temp2;

drop table temp;

Re: Merging Rows.....in different columns

Tue, 04 Mar 2014 04:55:38 GMT

Sorry For Late Comment,

Thanks it works....