topic Re: How to associate multiple columns with one? in QlikView

How to associate multiple columns with one?

Mon, 30 Mar 2015 17:33:44 GMT

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!

Re: How to associate multiple columns with one?

Anonymous — Mon, 30 Mar 2015 18:01:50 GMT

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 ;

Re: How to associate multiple columns with one?

Mon, 30 Mar 2015 18:23:07 GMT

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?

Re: How to associate multiple columns with one?

Anonymous — Mon, 30 Mar 2015 18:51:41 GMT

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

Re: How to associate multiple columns with one?

Mon, 30 Mar 2015 19:01:32 GMT

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!

Re: How to associate multiple columns with one?

Anonymous — Mon, 30 Mar 2015 19:08:26 GMT

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

Re: How to associate multiple columns with one?

Tue, 31 Mar 2015 14:22:21 GMT

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!

Re: How to associate multiple columns with one?

Mon, 06 Apr 2015 14:56:41 GMT

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!