Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
bartvanwetten
Contributor

Pivot in script

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

1 Solution

Accepted Solutions

Re: Pivot in script

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

View solution in original post

4 Replies

Re: Pivot in script

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

View solution in original post

bartvanwetten
Contributor

Re: Pivot in script

Hey Sunny,

works like charm man!!!

3rd time you made my day

Greetz from The Netherlands

Re: Pivot in script

You are keeping a count .

Best,

Sunny

pauldan2017
New Contributor III

Re: Pivot in script

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

ColumnIdSourceTableSourceIdCellValueModifiedDate
47964ReviewDate1Team541446022/06/201822/06/2018
47963ReviewFrequency1Team54144604522/06/2018
47959TeamMember1Team5414460Name of Team Member1 22/06/2018
47958CaseManager1Team5414460TRUE22/06/2018
47957ReviewDate1Team541446011/06/201822/06/2018
47956ReviewFrequency1Team541446018022/06/2018
47953TeamMember1Team5414460Name of Team Memaber 222/06/2018
Output
SourceIDReviewDate1ReviewFrequency1TeamMember1CaseManager1
541446022/06/201845Name of Team Member1 TRUE
541446011/06/2018180Name of Team Member2TRUE