topic Re: Re: Data transformation in QlikView

Data transformation

Anonymous — Thu, 10 Apr 2014 01:58:16 GMT

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

Re: Data transformation

sujeetsingh — Thu, 10 Apr 2014 03:37:39 GMT

Just go for Group By in Qlikview resident load.

Re: Data transformation

sujeetsingh — Thu, 10 Apr 2014 04:01:05 GMT

Here is a sample

Re: Data transformation

Anonymous — Thu, 10 Apr 2014 04:58:23 GMT

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 ?

Re: Data transformation

sujeetsingh — Thu, 10 Apr 2014 05:52:35 GMT

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

Re: Data transformation

Thu, 10 Apr 2014 06:02:03 GMT

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

Re: Data transformation

mato32188 — Thu, 10 Apr 2014 06:08:51 GMT

Hope helps

Re: Data transformation

sujeetsingh — Thu, 10 Apr 2014 06:09:24 GMT

Amir provided a new way to do it.

Well

Re: Data transformation

maxgro — Thu, 10 Apr 2014 06:19:23 GMT

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';

Re: Data transformation

its_anandrjs — Thu, 10 Apr 2014 06:19:30 GMT

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

Re: Data transformation

jagan — Thu, 10 Apr 2014 06:53:15 GMT

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.

Re: Data transformation

Thu, 10 Apr 2014 07:01:33 GMT

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;

Re: Data transformation

Anonymous — Thu, 10 Apr 2014 07:15:10 GMT

Sujeet,

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

Gan

Re: Data transformation

Anonymous — Thu, 10 Apr 2014 07:27:27 GMT

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 !

Re: Data transformation

Anonymous — Thu, 10 Apr 2014 08:37:55 GMT

You can also look at this..

Re: Data transformation

Anonymous — Thu, 10 Apr 2014 09:04:43 GMT

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

Re: Data transformation

mato32188 — Thu, 10 Apr 2014 19:04:42 GMT

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

Re: Data transformation

Anonymous — Thu, 10 Apr 2014 23:46:22 GMT

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

Re: Data transformation

Anonymous — Thu, 10 Apr 2014 23:55:24 GMT

Amir,

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

Regards

Gan

Re: Data transformation

Fri, 11 Apr 2014 02:14:53 GMT

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