topic Table transpose in QlikView

Table transpose

Anonymous — Tue, 23 Jan 2018 20:59:42 GMT

Hello QV Experts,

I have encountered quite challenging task. The issue is as follows:

I need to transform below table (in the script, I need qvd files with the below table and the new one)

ID	Task	StartTime	EndTime	EndTime-StartTime
01	A	11:00	12:00	1
01	B	11:30	13:30	2
01	C	11:15	12:15	1
02	A	11:30	14:30	3
02	B	10:40	12:40	2
02	C	11:00	12:00	1

As a result I need to create table as below:

ID	A.StartTime	A.EndTime	B.StartTime	B.EndTime	C.StartTime	C.EndTime	A.EndTime-StartTime	B.EndTime-StartTime	C.EndTime-StartTime
01	11:00	12:00	11:30	13:30	11:15	12:15	1	2	1
02	11:30	14:30	10:40	12:40	11:00	12:00	3	2	1

I have more than 300 000 record in the first table so the problem may be also performance of the script execution

I would appreciate any help how to achieve the final result

The transformation looks like kind of "reverse crosstable", but nothing comes to my mind to make it

Thank you in advance for your support/comment

Krzysztof

Anonymous — Tue, 23 Jan 2018 21:11:51 GMT

I think Generic Load is what you are looking for:

maxgro — Tue, 23 Jan 2018 21:47:37 GMT

LOAD

ID,

Task,

StartTime,

EndTime,

"EndTime-StartTime "

FROM

(html, codepage is 1252, embedded labels, table is @1);

SET t = 'T:';

for each i in 'A', 'B', 'C'

$(t)

load

ID,

StartTime as $(i).StartTime,

EndTime as $(i).EndTime,

"EndTime-StartTime " as "$(i).EndTime-StartTime "

Resident S

Where Task = '$(i)';

SET t = join (T);

NEXT

Anonymous — Wed, 24 Jan 2018 15:50:34 GMT

Thanks Josefina, I think this is the right solution for my case

Do you have any idea how to improve the slow performance when I am joining all table after Generic Load?

Unfortunatelly I cannot avoid this as I need qvd file with all data

Is there any solution for that?