Solved: Data transformation - Qlik Community

Anonymous · ‎2014-04-09

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

Report Inappropriate Content · ‎2014-04-10

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;

View solution in original post

sujeetsingh · ‎2014-04-09

Just go for Group By in Qlikview resident load.

sujeetsingh · ‎2014-04-10

Here is a sample

Anonymous · ‎2014-04-10

Sujeet,

Thanks for the solutions. My data has many fields and records and it will be messy to do this using expression. Is it possible to transform this during load into a table so that the fields can be easily referenced ?

sujeetsingh · ‎2014-04-10

Gan,

Go for generic reload.

Study of it through all sources available on net.

Understand the functioning and then implement in yours

For help

http://community.qlik.com/thread/10890

Report Inappropriate Content · ‎2014-04-10

Hi,

You might want to try this. Hope it would help.

Tab1:

LOAD Instance,

Question,

Response,

Date,

If(wildmatch(Question,'1*'),Instance) as InstanceT1,

If(wildmatch(Question,'1*'),Question) as QuestionT1,

If(wildmatch(Question,'1*'),Response) as ResponseT1,

If(wildmatch(Question,'1*'),Date) as DateT1

FROM

Delete\Q.Ans.xlsx

(ooxml, embedded labels, table is Sheet1);

Load

If(wildmatch(Question,'3*'),Instance) as InstanceT1,

If(wildmatch(Question,'3*'),Question) as QuestionT2,

If(wildmatch(Question,'3*'),Response) as ResponseT2,

If(wildmatch(Question,'3*'),Date) as DateT2

Resident Tab1

mato32188 · ‎2014-04-10

Hope helps

ECG line chart is the most important visualization in your life.

sujeetsingh · ‎2014-04-10

Amir provided a new way to do it.

Well

maxgro · ‎2014-04-10

another one using join

Source:

LOAD Instance,

Response as [Ticket Number],

Date

FROM

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

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

where left(Question,1)='1';

join (Source)

LOAD Instance,

Response as [Resolved ?]

FROM

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

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

where left(Question,1)='2';

join (Source)

LOAD Instance,

Response as [Rating]

FROM

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

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

where left(Question,1)='3';

its_anandrjs · ‎2014-04-10

Hi Cheong,

See the attached script which is in the through the inline table and you are able to load like this way try in your application

T1:

LOAD * Inline

[

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

];

LOAD Distinct

Instance as InstanceNew, Response as [1. Ticket Number]

Resident T1

Where Left(Response,1) = 'T';

Left Join

LOAD Distinct

Instance as InstanceNew, Response as [2. Resolved ?]

Resident T1

Where Match(Response,'Yes','No');

Left Join

LOAD Distinct

Instance as InstanceNew,KeepChar(Response,0123456789) as [3. Rating]

Resident T1

Where Len(Response) = 1;

Left Join

LOAD Distinct

Instance as InstanceNew,Date as newDate

Resident T1;

DROP Table T1;

EXIT Script;

See the snap shot for your final table with no duplicate in the table

Let me know if there is any problems

Regards

Data transformation

Related Topics