topic Re: Trying to convert rows to columns in QlikView

Trying to convert rows to columns

Wed, 10 Jul 2013 10:24:46 GMT

Hi,

I am trying to convert data in rows to columns but with no succes. I tried some of the silutions to similar questions.

The data that needs be converted is like:

Original
Datum	logon_id	actie	time_stamp
1-7-2013	abe118	Logon	8:10:18
1-7-2013	abe118	Logoff	12:24:43
1-7-2013	abe118	Logon	14:01:58
1-7-2013	abe118	Logoff	14:08:15
1-7-2013	abe118	Logon	14:29:29
1-7-2013	abe118	Logoff	16:26:49
1-7-2013	abi004	Logon	9:50:11
1-7-2013	abi004	Logoff	12:44:19
1-7-2013	abi004	Logon	13:12:37
1-7-2013	abi004	Logoff	16:20:05

And I would like to change it to:

Wanted
Datum	logon_id	Logon	Logoff
1-7-2013	abe118	8:10:18	12:24:43
1-7-2013	abe118	14:01:58	14:08:15
1-7-2013	abe118	14:29:29	16:26:49
1-7-2013	abi004	9:50:11	12:44:19
1-7-2013	abi004	13:12:37	16:20:05

The number of logon/logoff pairs can differ per id and per date.

I tried using a join but that resulted in 9 records for abe118 instead of 3

Anyone have an idea as how I should tackle it?

(btw we are still using qv 9)

Re: Trying to convert rows to columns

Gysbert_Wassenaar — Wed, 10 Jul 2013 10:32:47 GMT

As long as the source table is sorted by Datum, logon_id and timestamp so that a logon is always followed by a logoff you can do this:

LOAD RowNo() as id, Datum, logon_id, time_stamp as Logon

from ...sourcetable...

where actie = 'Logon';

join

LOAD RowNo() as id, Datum, logon_id, time_stamp as Logoff

from ...sourcetable...

where actie = 'Logoff';

Re: Trying to convert rows to columns

Wed, 10 Jul 2013 10:33:15 GMT

Test :

Load

Datum,

logon_id,

time_stamp As Logon

From.... Where actie='Logon' ;

Concatenate

Load

Datum,

logon_id,

time_stamp as Logoff

From.... Where actie='Logoff' ;

Re: Trying to convert rows to columns

Wed, 10 Jul 2013 11:29:56 GMT

Thank for both answers,

Both give the same reult:

Datum	logon_id	Logon	Logoff
1-7-2013	abe118	8:10:18
1-7-2013	abe118	14:01:58
1-7-2013	abe118	14:29:29
1-7-2013	abe118		12:24:43
1-7-2013	abe118		14:08:15
1-7-2013	abe118		16:26:49

which is not what I am looking for.

In the source table the data is indeed sorted by Datum,logon_id,time_stamp and every logon is followed by a logoff

Re: Trying to convert rows to columns

Gysbert_Wassenaar — Wed, 10 Jul 2013 11:38:24 GMT

Cool. I get a totally different result.

id	Datum	logon_id	Logon	Logoff
1	1-7-2013	abe118	8:10:18	12:24:43
2	1-7-2013	abe118	14:01:58	14:08:15
3	1-7-2013	abe118	14:29:29	16:26:49
4	1-7-2013	abi004	9:50:11	12:44:19
5	1-7-2013	abi004	13:12:37	16:20:05

Re: Trying to convert rows to columns

Wed, 10 Jul 2013 11:50:17 GMT

I found the problem.

The field Datum was originally derived from a timestamp as well so it looked like it was sorted but not quite as i expected.

I added a conversion step in between and now my results are the same as yours and I now have what I wanted.

Thanks!