Vertical to Horizontal(one column data to many columns)

[raajaswin]

Dear All,

Data:

Scenario:

1 work can go to many users , by the above example Workname 'A' is done by 'z','y','q'  and work 'B' is done by 'x','w'

Expected Output:

Here the maximum number of users doing one work is 3 (i.e work A is done by 3 people)

So I can create 3 columns in script and pull it or, I can create 3 expressions in chart and get the result. Which I have done in the attached file.

Problem:

For suppose 20 users are doing my work I can create 20 columns/expressions,But in my case the number of user doing a single work may vary it may go to any number in future so how can I achieve this output, can we do something with loop in script??

Can any one solve this?

Thanks in Advance,

Aswin.

[Gysbert_Wassenaar]

Create a new field in the script:

Data:

LOAD

     Work,

     User_doing,

     'User' & Autonumber(User_doing, Work) as UserWithNumber

     Dept,

     start_dt,

     end_dt

FROM

     ....source...

     ;

And then create a pivot table with Work and UserWithNumber as dimensions and pivot the UserWithNumber dimension so its values are displayed as columns instead of rows.

[sunny_talwar · Accepted solution]

Try this:

A:

LOAD *,

  AutoNumber(RecNo(), Workname) as Key;

LOAD * INLINE [

    Workname, User_doing, Dept, start_dt, end_dt

    A, z, WE, 1/1/2016, 2/1/2016

    A, y, WE, 2/1/2016, 3/1/2016

    A, q, WE, 4/1/2016,

    B, x, RT, 1/1/2016, 3/1/2016

    B, w, RT, 1/1/2016,

];

FinalTable:

LOAD Distinct Workname

Resident A;

FOR i = 1 to FieldValueCount('Key')

  LET vField = FieldValue('Key', $(i));

  Left Join(FinalTable)

  LOAD Workname,

  User_doing as User$(i)

  Resident A

  Where Key = $(vField);

NEXT

[raajaswin]

Hi Gysbert,

Thanks for the reply

I got this, but my output not only has user it has dept, startdate and end date also

for example I have added dept now in the below image, same way I need respective start date and end date to be displayed

[raajaswin]

Sunny ,

Here after reloading my application I come to know how many columns are formed and I will manually pull it in chart.

But in my case consider a scenario, when i am developing maximum number of columns is 3 and later maximum number of columns become 5 so again i am missing the two newly formed columns in my report.

Is there any way to display those columns

[sunny_talwar]

I am not sure I understand what is the issue?

[raajaswin]

There is no issue with the solution it is working perfectly.

we created the loop only because we dont know the maximum number of users doing the same job.. Consider the same data. Workname 'A' is done by three users z,y,q so our loop will create three columns User1,user2 and user3. And In chart I will pull those columns User1, User2, User3 . Now I deployed the app.

Consider now in the next day refresh same Workname 'A' is done by an additional user (i.e a new user('p') got added), Now Work 'A' is done by 4 person as per new data, so our loop will accordingly create for columns User1, User2 , User3 and User4.   But I have pulled only three 3 columns in the chart of the deployed app. Though our loop works perfectly and creating new new columns it will not display in chart. that is the problem.. I need to display User4 also in the chart that is 'Dynamic addtion of columns to chart based on loop'  .

Hope you understood

Thanks in Advance

Aswin

[sunny_talwar]

Then I believe it would be better to do this on the front end like Gysbert mentioned using a Pivot table. Do you have any issue using Pivot Table?

Other solution could be to add 100 columns already but conditionally show them only when they are available. I randomly picked 100, but pick a really high number based on your data which you might think will never be achieved. So if you think the max users won't exclude 10 in any circumstances, then may be create 12 expressions (just in case).

[raajaswin]

Thanks Sunny,

As of now we are using conditional expressions in front end only. I have posted this question to know is there any way to automate it...   Pivot will not help because not only user but dept, start date end date should also be shown as columns..

[sunny_talwar]

You can have a pivot table where user, dept, start date and end date are all columns and it would still layout nicely (I believe). Have you tried playing around with the pivot table idea at your end? If you have a working sample, I can show you how this might be done