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

[mato32188]

Hi Cheong,

try this... I used previous record from date field to modify your "1 second slack" for Question 2. Resolved... based on previous date record...not sure, if such "logic" could be generally used for your data set

Or...u can use pivot table to avoid seeing dates ... if necessary /not solution/

Regards M

[#3]

Hi Cheong,

This is easily fixed by adding a where clause in the load statement.

Just replace:

//create pk list

fields:

load distinct Instance, Date

resident datagroup;

with

//create pk list

fields:

load distinct Instance, Date

resident datagroup

// Added where clause to load statement to filter out only the ticket number date/time

Where left(Question,1)=1;

See attached files for working example.

[Anonymous]

Martin,

Thanks !  That is a great and simple solutions !

[Anonymous]

Martin B,

Thanks for the suggested fix

[Anonymous]

Thanks everyone for chipping in.  This is such a helpful and great forum. I learnt a lot from the different approaches to solving the problem.