topic Re: Numerical problem in QlikView

Numerical problem

datanibbler — Thu, 24 Oct 2013 12:20:39 GMT

Hi,

I will try to formulate my problem precisely enough so someone can hopefully help me. Putting together some dummy_data would require time that I haven't actually got, but I'd do it if necessary, of course.

- I have personell data: Every emp has a Nr. and a "personell_unit" he belongs to.

- Due to my SQL_SELECT out of two DB tables, each employee ends up with n records:

- one where he belongs to "PU" 1 - this branch (level_1/ top-level)

- one where he belongs to PU 12 - this plant (there are two plants/halls to this branch)

- one where he belongs to PU 125 - one area in the plant

- one where he belongs to PU 1253 - early_shift (level_4)

- There might even be more.

- In my script, I actually have a max() fct. at one point, so this emp should remain with only one record as belonging to PU 1253.

=> To enable managers to see the combined figures for both shifts of one area, I have generated a mapping table where all PU-values are assigned the same "bigger_area", so when the user selects that, he sees all PU-codes assigned to that.

<=> My problem is now such that when I select one plant, then I can select any of the "bigger_areas" in that plant - and in that list, there is one PU-value (say, 125, to stick with that example) - that should not be there because the max() function should actually throw it out and keep only PU 1253

The numbers of course are different, this was just an example for the structure of my problem.

I will attach a txt file with the two LOAD statements where my problem lies.

Thanks a lot!

Best regards,

DataNibbler

P.S.: Strangely, when I look at the nr_of_rows, the table in the second LOAD statement does get reduced by a few records, so something is happening - but I'm not sure what...

Re: Numerical problem

datanibbler — Thu, 24 Oct 2013 13:35:22 GMT

Hi,

in the individual apps, I can just as well circumvent the problem by just taking those "superordinate" areas out of the filtering_inline_table, there is a comment in the base list to know where to do that.

I'd still like to know why - it works perfectly in precisely this way (well, probably not...) in the app I built for HR.

There I get 4 emps in that area - all in either shift_1 or shift_2 <=> in my new app (a template from where this will be propagated into all other apps) there are 11 - shift_1, shift_2, the same number in the superordinate_area plus some more in areas I don't display - and which I never had in my filtering_table...

Btw, what is this big white bunny with the tophat doing there...

Best regards,

DataNibbler

Re: Numerical problem

datanibbler — Fri, 25 Oct 2013 06:51:33 GMT

Hello,

to be more precise:

- The max() function should, per employee, filter out all but one records - only the record with the largest numerical PU - the smallest unit - should remain.

<=> It is of course possible that the superordinate area is not filtered out if there are employees not assigned to the smaller areas below, but only to that superordinate area - in which dase that PU would be the one with the largest numerical value

<=> In the area I looked at yesterday, that is not the case - as I said, I have one app where it works perfectly and that I can use as reference plus I am in steady contact with HR about this.

Mysteries in the dark...

Re: Numerical problem

datanibbler — Fri, 25 Oct 2013 08:37:08 GMT

Oh my - now I have found the correct way to do it - in one of two possible cases anyway (where there are employees assigned only to the superordinate area, so I don't have to throw out anything) - without knowing what I had done wrong in the first place... very annoying that is... Not understanding errors condemns one to make the same mistake again eventually...

I still have to test the other case.