topic Re: How to convert columns to rows? in QlikView

How to convert columns to rows?

josephinetedesc — Fri, 23 Mar 2018 15:47:58 GMT

I have a table that I can produce in QlikView that looks like this:

Name	Clock1	type	count(type)
John	28/08/2014	stop	1
John	11/11/2014	reset	1
John	16/12/2014	stop	1
John	10/02/2015	reset	1
John	17/02/2015	stop	1
Paul	1/03/2016	stop	1
Paul	10/03/2016	reset	1
Paul	24/03/2016	stop	1
George	28/08/2014	stop	1
George	24/03/2016	reset	1
George	10/03/2016	stop	1
George	1/03/2016	reset	1
George	17/02/2015	stop	1
George	10/02/2015	reset	1
George	16/12/2014	stop	1
George	11/11/2014	reset	1

I would like it to look like this:

Name	stop1	reset1	stop2	reset2	stop3	reset3	stop4	reset4	stop5
John	28/08/2014	11/11/2014	16/12/2014	10/02/2015	17/02/2015
Paul	1/03/2016	10/03/2016	24/03/2016
George	28/08/2014	24/03/2016	10/03/2016	1/03/2016	17/02/2015	10/02/2015	16/12/2014	11/11/2014

I suspect I will need hight level programming skills?

The headings stop1, reset 1, stop2, reset2 etc could just as easily be 1,2, 3, 4 etc

Is there something I could do with count(type) column? - I only did this so that I could make a pivot table.

If you could point me in the right direction I will research some more.

Thank you

Re: How to convert columns to rows?

bramkn — Fri, 23 Mar 2018 15:54:28 GMT

Crosstable maybe? this does work the other way around. Not sure if it will work for you as well. But it is a direction.

Re: How to convert columns to rows?

Peter_Cammaert — Fri, 23 Mar 2018 16:08:46 GMT

Getting some sort of order out of your original table is quite a challenge.

If we consider just Name = George, then how do the Resets relate to their corresponding Stops? The Clock1 values are pretty useless, as that would mean that for George his last line would match the first one and reset4 in your target table would suddenly be called reset1...

Can you explain what the logic is behind the combinations in you example?

[Edit] Sorry, no 'Starts', just 'Stops'

Re: How to convert columns to rows?

MarcoWedel — Fri, 23 Mar 2018 23:21:07 GMT

Hi,

besides the sorting issue Peter already mentioned, maybe one solution could be:

table1:
LOAD RecNo() as ID,
    Name,
    Clock1,
    type&AutoNumber(RecNo(),Hash128(Name,type)) as type
FROM [https://community.qlik.com/thread/295890] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Re: How to convert columns to rows?

josephinetedesc — Sat, 24 Mar 2018 02:08:09 GMT

Ah the logic!

The idea is that those "pesky users" want only "real" time to be used in their kpi data. So they may do a task but take time out for a reason and they do not want that missing time to be calculated.

So John - he might just do a task in 2 days,

Paul - might take 10 days - but he had a break of 4 days (and those 4 days are not necessarily consecutive days)

What I meant was that I can sort the dates in order but if I can get them into columns I can just rename the column names with no impact.

Hope that makes sense!

Re: How to convert columns to rows?

josephinetedesc — Sat, 24 Mar 2018 02:10:43 GMT

Thank you Marco

I cannot open QlikView files from home - so I will need to do this from work!

I love working with QlikView - QlikSense -not so much

Re: How to convert columns to rows?

josephinetedesc — Sat, 24 Mar 2018 12:24:43 GMT

so to understand:

function Hash128 is to make the number in type and the Name unique - it does this on the fly. ?????

I make a pivot table and it works ...

Now another question: I used this line in my code

'stop ' &AutoNumber(RecNo(),Hash128(Name ,'stop')) as type - this gives stop 1

I would like it to look like this: 1 stop (that way when I sort the by type I will get them in order ie 1 stop, 1 reset, 2 stop, 2 reset etc.

Thank you Marco

Re: How to convert columns to rows?

MarcoWedel — Sat, 24 Mar 2018 16:46:27 GMT

Hi,

Yes, I used the Hash128() function to get unique AutoIDs for the Autonumber() function.

As for your second question, you could as well skip the Hash function when using a static 'stop' parameter.

AutoNumber(RecNo(),Name) should already result in your required sorting order.

hope this helps

regards

Marco