topic Re: E() function doesn't work as expected. in QlikView

E() function doesn't work as expected.

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone, hope somebody can help. First of all, let me excuse myself for the title of the question, but I wasn't able to find out anything enough describable.

I got stuck at certain point I don't know which direction to take. Let me explain.

I got a table loaded with multiple records of several fields, mainly OrderNo, Worker, Room. So I have Room1 and Room2, and an order can be made with items from one room or from both, and it can be made by one worker or by two.

What I need is a straigth table like in the picture. I've been trying with the E Function like this

Count( DISTINCT {$< OrderNo = E({$<Room={1}>})>}  OrderNo)

to get the Orders of the Room2 that doesn't have Room1 but I don't get what I want. How can I get all the orders of a certain worker that just have Room2 for that worker, No matters if the same order have another worker in Room1.

Having 4 records like these, the result should be:

Any direction to follow would be much appreciated.

Thanks in advance for your time

Re: E() function doesn't work as expected.

swuehl — Tue, 27 Jun 2017 08:46:44 GMT

I think you could create a combined key of order and worker in your script and use that field instead of OrderNo alone in your set analysis.

Re: E() function doesn't work as expected.

Anil_Babu_Samineni — Tue, 27 Jun 2017 08:58:07 GMT

Why you want that complex either we can use this?

Count({<Room -= {1}>} DISTINCT OrderNo)

Re: E() function doesn't work as expected.

Anonymous — Tue, 27 Jun 2017 09:11:02 GMT

You mean something like this?:

LOAD

note&'-'&UserId AS CustomKey,

RESIDENT TABLE;

And then use it this way to get the orders made in the Room2

Count( DISTINCT {$<CustomKey=P({$<Room={"2"}>})>} CustomKey)

I've tried it but it doesn´t work because If I have the following records

It will return 2 as result (2000 and 2001) and I just want the 2001 order. Because is doesn't have Room1 for the same order and same worker, Is any way to evaluate at order level and say: Count this order if it doesn`t have Room1 for this worker (same worker)

Thanks for you time

Re: E() function doesn't work as expected.

Anonymous — Tue, 27 Jun 2017 09:29:58 GMT

Hi Anil. It doesn't work either.

Re: E() function doesn't work as expected.

swuehl — Tue, 27 Jun 2017 10:09:38 GMT

No, I was talking about just replacing OrderNo with your new field in your original expression, something like

Count( DISTINCT {$< CustomKey= E({$<Room={1}>})>} OrderNo)

Re: E() function doesn't work as expected.

Anonymous — Tue, 27 Jun 2017 10:58:11 GMT

That's right!! Thank you very much!!