Pivot in script

[bartvanwetten]

Dear all,

Can someone help me out on this challenge?

The left table in the attached qv shows the sourcedata (qvd attached).

The right pivot in the attached qv shows how it has to be converted.

What I need is a script which creates a qvd with what you see in the right table. (The script should do the job.)

So the data of the 'Station' field should become the fieldnames.

I have both attached the qvd and the qvw.

Any help appreciated!

Kind regards,

Bart

[sunny_talwar · Accepted solution]

Try this:

Measures:

LOAD Station,

     TimeStampNew,

     Measure

FROM

[Test.qvd]

(qvd);

NewTable:

LOAD Distinct TimeStampNew

Resident Measures;

For i = 1 to FieldValueCount('Station')

  LET vStation = FieldValue('Station', $(i));

  Left Join (NewTable)

  LOAD Distinct TimeStampNew,

  Measure as $(vStation)

  Resident Measures

  Where Station = '$(vStation)';

NEXT i

DROP Table Measures;

STORE NewTable into Test2.qvd (qvd);

[bartvanwetten]

Hey Sunny,

works like charm man!!!

3rd time you made my day

Greetz from The Netherlands

[sunny_talwar]

You are keeping a count .

Best,

Sunny

[Anonymous]

Hi Can you let me know how to achieve the following task

I have been trying some code examples and it's gives me duplicate row in the results

ID	ColumnId	SourceTable	SourceId	CellValue	ModifiedDate
47964	ReviewDate1	Team	5414460	22/06/2018	22/06/2018
47963	ReviewFrequency1	Team	5414460	45	22/06/2018
47959	TeamMember1	Team	5414460	Name of Team Member1	22/06/2018
47958	CaseManager1	Team	5414460	TRUE	22/06/2018
47957	ReviewDate1	Team	5414460	11/06/2018	22/06/2018
47956	ReviewFrequency1	Team	5414460	180	22/06/2018
47953	TeamMember1	Team	5414460	Name of Team Memaber 2	22/06/2018
Output
SourceID	ReviewDate1	ReviewFrequency1	TeamMember1	CaseManager1
5414460	22/06/2018	45	Name of Team Member1	TRUE
5414460	11/06/2018	180	Name of Team Member2	TRUE