Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Table transpose

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)

IDTaskStartTimeEndTimeEndTime-StartTime
01A11:0012:001
01B11:3013:302
01C11:1512:151
02A11:3014:303
02B10:4012:402
02C11:0012:001

As a result I need to create table as below:

IDA.StartTimeA.EndTimeB.StartTimeB.EndTimeC.StartTimeC.EndTimeA.EndTime-StartTime B.EndTime-StartTime C.EndTime-StartTime
0111:0012:0011:3013:3011:1512:15121
0211:3014:3010:4012:4011:0012:00321

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

3 Replies
Anonymous
Not applicable
Author

I think Generic Load is what you are looking for:

Use cases for Generic Load | Qlikview Cookbook

maxgro
MVP
MVP

S:

LOAD

ID,

    Task,

    StartTime,

    EndTime,

    "EndTime-StartTime "

FROM

[https://community.qlik.com/thread/288838]

(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
Not applicable
Author

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?