QlikView App Dev

[pitutiti]

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

[swuehl]

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.

[Anil_Babu_Samineni]

Why you want that complex either we can use this?

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

[pitutiti]

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

[pitutiti]

Hi Anil. It doesn't work either.

[swuehl · Accepted solution]

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) 

[pitutiti]

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