How to associate multiple columns with one?

[#1]

Hello,

I'm trying to track the time of multiple people in our workflow. Each person has their own column for the time they take on a task. Then, there's a column with each person's name.

My goal is to map each time column to the person so that when a task is selected that had two people who worked on it, for example, those two people are active in my text box and the rest are grayed out.

I've attached an Excel mockup of what my data source looks like -- can anyone advise?

Many thanks!

[Anonymous]

Would transforming it something like this help ?

Temp1:

LOAD Person,

    [Person A Time],

    [Person B Time],

    [Person C Time]

FROM

(ooxml, embedded labels, table is Sheet1);

Temp2:

CrossTable([Person A Time], Data)

LOAD Person,

  [Person A Time],

    [Person B Time],

    [Person C Time]

FROM

(ooxml, embedded labels, table is Sheet1)

;

Drop table Temp1 ;

Data :

NoConcatenate

load

  RowNo() ,

  num(Data) as Time ,

  Person

resident Temp2

;

Drop table Temp2 ;

[#3]

Thanks! This seems to achieve what I'm looking for. One more question, though -- how do I integrate this with other fields as part of a larger table?

[Anonymous]

Something like this ?

Data:

CrossTable([Label],  Time , 3 )

LOAD

  UPPER ,

  Lower ,

  Person,

  [Person A Time],

  [Person B Time],

  [Person C Time]

;

LOAD

  UPPER(Person) as UPPER ,

  Lower(Person) as Lower ,

  Person,

  [Person A Time],

  [Person B Time],

  [Person C Time]

FROM

(ooxml, embedded labels, table is Sheet1);

[#5]

Thanks for that! This looks better. I did notice one thing when testing the matching of People and Times, though -- if I select Person C, "50" and "120" are active. Ideally, "60" should be active, since this is in the Person C Time column.

Similarly, "60" and "120" should be active if I select Person A. Does this make sense?

Let me know if you need any clarity, and thank you once again for your help!

[Anonymous]

I was unsure why you have the "Person" field then the "Person X Time" fields.  Unsure how they relate to each other.

[#7]

I apologize, I should have clarified -- "Person A" should correspond to the "Person A Time" column, "Person B" to "Person B Time," etc.

Is this feasible? Please advise, and thanks for your help!

[#8]

Hello, Bill/All,

Can someone advise how I can match each Person (e.g., Person A) with their respective Time column (Person A Time)?

Thanks so much for your help!