topic I need to set a analysis on the script, please help. in QlikView

I need to set a analysis on the script, please help.

Fri, 22 Jan 2016 16:07:06 GMT

Hello!

I asked this question long ago, but the answers were more directed on solving the problem on the dashboard, rather than the script, and at that time I choose to do that, but now the data base has grown a little bit more, and I think it will grow even more, so I need to do this on the script.

The problem is this: Every month I need to count the exact number of employees per user tittle, that are working in the company, this is only the first step to do other calculations, so it need to be accurate. The issue appears when I have more than one user tittle -it should be always only one-, attached for one employee, in those cases I need to see which one of the two has the most working hours and then choose that with the higher work load. for example: "John" is a developer, and has been a developer for almost three months :November, December and January, but "John" gets promoted at the beginning of January to supervisor, so in that month (January), I would have two identical emails for "John", one per each user tittle. What I do here is that I look at the "working hours", and I decide what "Jhon" was based on that, here lets say that "Jhon" reported only 30 hours for developer and the rest of the 160 (that is 130 hours), are reported as supervisor, so under the light of this evidence, the developer "John" would get erased and wont be counted as an employee.

Please Help me, I am attaching the file with the data.

Thank you very much.

Re: I need to set a analysis on the script, please help.

Anonymous — Fri, 22 Jan 2016 17:17:38 GMT

Hi Jesus, could you send us the data source? (Roles desde Tenrox.xls)

Or a sample of this?

--------------------------------------

Hola, podrías mandarnos una muestra del excel, creo que se como puedes calcular eso.

Saludos.

Re: I need to set a analysis on the script, please help.

Gysbert_Wassenaar — Sun, 24 Jan 2016 18:39:33 GMT

Set analysis only works in chart expressions. It cannot be used in the script. You'll have to calculate the max month per employee and then for each max month determine the max hours. You can flag those records with something like this:

JOIN (Tablaroles)

LOAD

u.EMAILADDRESS,

max(mestenrox) as mestenrox,

1 as IsMax_mestenrox

RESIDENT

Tablaroles

GROUP BY

u.EMAILADDRESS

;

JOIN (Tablaroles)

LOAD

u.EMAILADDRESS,

mestenrox,

IsMax_mestenrox,

max([Total Horas]) as [Total Horas],

1 as IsMax_Total_horas

RESIDENT

Tablaroles

WHERE

IsMax_mestenrox =1

GROUP BY

u.EMAILADDRESS,

mestenrox,

IsMax_mestenrox

;

Re: I need to set a analysis on the script, please help.

Anonymous — Mon, 25 Jan 2016 14:45:16 GMT

Hi Jesus,

I found your other thread just a while ago. Let me post your example app here, too, with the proposed solution. It's not exactly same as the one you have attached here but in principle the problem stays the same. So the solution should also apply.

rgds,

Artur