Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

krzftyri
New Contributor

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
jsf_fasoli
Contributor II

Re: Table transpose

I think Generic Load is what you are looking for:

Use cases for Generic Load | Qlikview Cookbook

MVP
MVP

Re: Table transpose

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


krzftyri
New Contributor

Re: Table transpose

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?

Community Browser