Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Avoid double counting issue

Highlighted
5 - Atom

Hello all,

 

I have a table containing a few thousand records each relating to a construction project. 

 

Here is a sample of the data containing the relevant fields:

ID

NAME

TOTAL HOMES

PRIVATE HOMES

1

Greenwich Peninsula – Master Consent

1000

600

2

Greenwich Peninsula – Phase 1

400

200

3

Greenwich Peninsula – Phase 2

300

100

4

Riverside

300

150

5

Wembley Park - Master Consent

400

400

 

Due to the nature of planning permissions in the UK, master development projects can contain smaller individual phases of the project. This creates a problem for me - if I sum [Total Homes] in the above sample I double count 700 homes. This is because 700 of the 1000 [Total Homes] in 'Greenwich Peninsula - Master Consent' are accounted for in the two phase records (rows 2+3).

 

In my data table there are three possible scenarios:

 

  1. A master consent record with one or more phased records.
  2. A master record with no phases.
  3. A record which is neither a master or phase.

(Phases without a master do not exist)

 

To avoid double counting in the database I wish to subtract [Total Homes] and [Private Homes] for each phase record from [Total Homes] and [Private Homes] for the relevant Master Consent. e.g.  'Greenwich Peninsula – Master Consent' would end up with 300 Total Homes and 300 Private Homes after subtracting the two Greenwich Peninsula Phases in the sample table:

 

ID

NAME

TOTAL HOMES

PRIVATE HOMES

1

Greenwich Peninsula – Master Consent

300

300

2

Greenwich Peninsula – Phase 1

400

200

3

Greenwich Peninsula – Phase 2

300

100

4

Riverside

300

150

5

Wembley Park - Master Consent

400

400

 

I do not wish to delete any records - I just need to re-calculate [Total Homes] and [Private Homes] in the relevant master consent records. If the Master record has no phases then I wish to leave it unchanged.

 

Master consents and their phases always have the same structure “XXXXXXXXXX – Master Consent” and “XXXXXXXXXX – Phase X”. Note that the text before the hyphen is always matching between masters and their phases.

 

I am absolutely stuck here and would really appreciate any help.

 

Many thanks.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

This is relatively straight forward to solve, just requites some 'data transformation'.

 

Does this solution work for you?

 

Ben

Highlighted
6 - Meteoroid

Hi, i have tried using some transformations. Please let me know if it works.

 

 

 

Thanks,

Harika K

Highlighted
13 - Pulsar

Attaching a sample workflow for your case.  Hope this is helpful. 

 

WorkflowWorkflowResultsResults

Highlighted
5 - Atom

Thank you very much Ben. This is extremely useful.

 

One small issue that I did not make clear is that the ID is the only unique identifier in the table. There are projects which share the same name but are not the same project. Therefore the final join in the 'Example.yxmd' would cause an issue in the data I am using where the wrong schemes may be joined to different ID's. Do you have any ideas on how to get around this issue?

 

I should have submitted the table like this where 'Riverside' is the name of two different projects:

 

ID

NAME

TOTAL HOMES

PRIVATE HOMES

1

Greenwich Peninsula – Master Consent

1000

600

2

Greenwich Peninsula – Phase 1

400

200

3

Greenwich Peninsula – Phase 2

300

100

4

Riverside

300

150

5

Wembley Park – Master Consent

400

400

6

Riverside

1000

1000

 

I would guess it is filtering anything without "master" or "phase" at the start of the workflow and performing a union at the end? Keen to see how you approach the problem.

 

Again many thanks for your time and hugely appreciative of the solution you have already submitted.

 

Ed

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi Ed,

 

Amended version of the logic to account for this, see attached.

 

Ben

Highlighted
5 - Atom

Thanks so much Ben much appreciated!

Labels