Data transformation

[Anonymous]

I have a data source that looks like this :

Instance	Question	Response	Date
1	3. Rating	5	2014/01/14 23:27:38
1	1. Ticket Number	T001	2014/01/14 23:27:38
1	2. Resolved ?	Yes	2014/01/14 23:27:38
2	3. Rating	4	2014/02/14 17:37:57
2	1. Ticket Number	T002	2014/02/14 17:37:57
2	2. Resolved ?	Yes	2014/02/14 17:37:57
3	3. Rating	1	2014/01/03 00:54:57
3	2. Resolved ?	No	2014/01/03 00:54:57
3	1. Ticket Number	T003	2014/01/03 00:54:57
4	3. Rating	3	2014/03/11 21:14:27
4	1. Ticket Number	T004	2014/03/11 21:14:27
4	2. Resolved ?	Yes	2014/03/11 21:14:27

Can QlikView's transformation function transform the data to this format ?

Instance	1. Ticket Number	2. Resolved ?	3. Rating	Date
1	T001	Yes	5	2014/01/14 23:27:38
2	T002	Yes	4	2014/02/14 17:37:57
3	T003	No	1	2014/01/03 00:54:57
4	T004	Yes	3	2014/03/11 21:14:27

[jagan]

Hi,

Please try below script, it is dynamic, also please find attached file for solution

Temp:

LOAD Instance,

    Question,

    Response,

    Date

FROM

[http://community.qlik.com/thread/113801]

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

Date:

LOAD DISTINCT

  Instance,

  Date

RESIDENT Temp;

DROP FIELD Date From Temp;

Data:

Generic Load

*

RESIDENT Temp;

Set vListOfTables = ;

  For vTableNo = 0 to NoOfTables()

      Let vTableName = TableName($(vTableNo)) ;

      If Subfield(vTableName,'.',1)='Data' Then

        Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

      End If

  Next vTableNo

  Let vTableCount = 0;

  For each vTableName in $(vListOfTables)

    If vTableCount = 0 Then

      CombinedGenericTable:

      NoConcatenate

      Load *

      Resident [$(vTableName)];

    ELSE

      LEFT JOIN(CombinedGenericTable)

      Load *

      Resident [$(vTableName)];

    END If

      Drop Table [$(vTableName)];

      LET vTableCount = vTableCount + 1;

  Next vTableName

DROP TABLE Temp;

LEFT JOIN (CombinedGenericTable)

LOAD

*

RESIDENT Date;

DROP TABLE Date;

Regards,

Jagan.

[#3 · Accepted solution]

Found a solution at this link: Row values as fields (opposite of crosstable?)

This solution will loop through all the values in the "Question" column, and create a new column.

So you don't have to think about changing your script, even if you add more questions at a later stage.

Corrected the script, so it would fit your needs:

datagroup:

LOAD Instance,

     Question,

     Response,

     Date

FROM

(ooxml, embedded labels, table is Sheet1);

//import field list and count number of fields

masterfields:

LOAD distinct Question

resident datagroup;

//count no of distinct Questions

let vfieldnos# = fieldvaluecount('Question');

//create pk list

fields:

load distinct Instance, Date

resident datagroup;

//set loop to zero

let counter#=0;

//loop through every Question

for counter#=0 to ($(vfieldnos#)-1)

//createfield names

let vfield = peek('Question',$(counter#),'masterfields');

//join data to pk list

join(fields)

load Instance,

Response as [$(vfield)]

resident datagroup where Question='$(vfield)';

next counter#

//cleanup stage

drop table masterfields;

drop table datagroup;

[Anonymous]

Sujeet,

Thanks for the link.  That is how my data is stored.  I will go through it.

Gan

[Anonymous]

Thanks everyone for the prompt response and providing different solutions. !

The hard coded join method is the simplist and easy to understand if there are only a few fixed fields. If there are more fields or possibility of new fields, the generic load method seems like the way to go.

I will try out both solutions and see which works better for my data.

Appreciate all the help !

[Anonymous]

You can also look at this..

[Anonymous]

Martin,

I tried you solutions and it works.  The only minor issue is within the same instance there are different dates thus duplicate records are created.

How can I use the date from the Ticket Number record ?

Thanks

Gan

[mato32188]

Im sorry, but I dont see any duplicates within the instance for dates. Maybe dont understand your reply clearly, ... Either way how to remove duplicates is to use DISTINCT command.

Regards, M

[Anonymous]

Martin,

My original sample has all the same date but my actual data has different dates within the same instance

See new sample data below :

Instance	Question	Response	Date
1	3. Rating	5	2014/01/14 23:27:38
1	1. Ticket Number	T001	2014/01/14 23:27:38
1	2. Resolved ?	Yes	2014/01/14 23:27:38
2	3. Rating	4	2014/02/14 17:37:57
2	1. Ticket Number	T002	2014/02/14 17:37:57
2	2. Resolved ?	Yes	2014/02/14 17:37:57
3	3. Rating	1	2014/01/03 00:54:57
3	2. Resolved ?	No	2014/01/03 00:54:58
3	1. Ticket Number	T003	2014/01/03 00:54:57
4	3. Rating	3	2014/03/11 21:14:27
4	1. Ticket Number	T004	2014/03/11 21:14:27
4	2. Resolved ?	Yes	2014/03/11 21:14:26

[Anonymous]

Amir,

Thanks for the suggestion.  I don't quite understand how your solution work. Can you explain ?

Regards

Gan

[#11]

Hi Gan,

There are sort of Categories in Question column where I did a wildmatch on these categories and did a Resident where Rating is returned only.

Thanks,

Aamir