topic Re: Delete duplicate records using alternative states in QlikView

Delete duplicate records using alternative states

Wed, 10 May 2017 18:13:44 GMT

Hi all,

I have a problem with alternative states.

I'm using alternate states to create a list of users that they have attended an event to create a list of clients interested in the next smae event. I have the next data set:

We use 2 alternatives states because I want to create diferents groups of posibilities. When I create the final list, I use the next expression Count([State 1] Name) + Count([State 2] Name). in the State 1 I filter for Event A and in State 2 for Event B.

The final table should have all the fields but when I have the same attende in two events (example: Jon) only should show once, I use an another field that define the priority of each state (another field not relted witth principal table) to priorice the state which have more weight.

Can you help me with this situation?

Re: Delete duplicate records using alternative states

rwunderlich — Wed, 10 May 2017 22:10:35 GMT

Use the DISTINCT keyword to count each name only once.

Count{[State 1] + [State 2]} DISTINCT Name)

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Delete duplicate records using alternative states

Digvijay_Singh — Wed, 10 May 2017 22:24:00 GMT

See if this sample helps -

Re: Delete duplicate records using alternative states

Thu, 11 May 2017 06:39:27 GMT

Hi Digvijay,

Yes this is te correct functionalitybut in the cases when we have an attendee he is in two events, we xdefine a priority for each state.

I attach an image. I have the problema to get the correct information using this fiel priority.

Example:

Jon asist Event A and B, if in stateB I select priority = 1, in the final table should show the register with the information

Jon - Event B - Company 1 - 28 (because the pirority for State B is bigger than State A

Thank you for your help

- Aimar

Re: Delete duplicate records using alternative states

Digvijay_Singh — Thu, 11 May 2017 16:52:09 GMT

See this, looks bit confusing but seems to be working, as long as single priority in each state is selected -

Re: Delete duplicate records using alternative states

Fri, 12 May 2017 06:54:57 GMT

Thank you very much Digvijay!!

This is the solution!

- Aimar