topic Key field question in QlikView

Key field question

Tue, 07 Dec 2010 17:20:03 GMT

Hi QV Community,

I have the following question:
I am loading two tables and Left Join them. One table has estimating data and the other table has costing data (please see attached excel file). My key field is the cost center (need this for my reports as key). To generate additional reports, I would need basically a different key by loading the data, meaning the job number.

I have the job number in both tables (just a different name). In the attached QV file I loaded the Excel file and I would like to be able to select the JobE and see the right costing and estimating times attached to it.

Right now, I can do it for the cost center, but not by job. My question, is this possible and if yes, how could I do it? I played around with Set Analysis, but without any success.

Thanks for your help,

Oliver

Key field question

pover — Tue, 07 Dec 2010 17:47:45 GMT

Try joing the 2 tables with both keys in the following fashion:

LOAD JobE & '_' & [Cost Center] as Key,
JobE,
Customer,
[Cost Center],
[Estimating Time]
FROM
Book2.xls
(biff, embedded labels, table is Jobs$);

LOAD Job & '_' & [Cost Center] as Key,
//Job,
//[Cost Center],
Time
FROM
Book2.xls
(biff, embedded labels, table is JobCosting$);

Regards.

Key field question

Tue, 07 Dec 2010 17:54:52 GMT

That doesn't work. The problem I have is, that the job can be estimated with a different cost center. Meaning, the job can be estimated with Cost Center A and run later for the costing on Cost Center B.

Thanks,

Oliver

Key field question

pover — Tue, 07 Dec 2010 18:29:51 GMT

Oliver,

Try then to use the following formula for Job Costing. A screenshot of the result is below.

sum({$<[JobE]=,Job=P(JobE)>} total Time)

Regards.

Key field question

Tue, 07 Dec 2010 21:28:01 GMT

Hi Karl,

this works, thanks!

Oliver