Forum Discussion

duncanelliot's avatar
duncanelliot
Frequent Visitor
9 years ago
Solved

Append and shape data

Hi there,   My requirements are fairly complex and am hoping someone might be able to help out.   At a high level, I want to plot user activity on a map. - Users can either register or transact....
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    duncanelliot

     

    It seems that you're looking for a UNION calculated table. What is the logic to determine 1st transaction and so on.

     

     

    union table =
UNION (
    SELECTCOLUMNS (
        Locations,
        "Table", "Location",
        "Created on", "",
        "Created on_Location", Locations[Created On ],
        "event type", "event type",
        "lat", Locations[Lat],
        "long", Locations[Long],
        "userid", "",
        "locationid", Locations[Id]
    ),
    SELECTCOLUMNS (
        Transactions,
        "Table", "Transactions",
        "Created on", Transactions[Created On ],
        "Created on_Location", "",
        "event type", "event type",
        "lat", Transactions[Lat],
        "long", Transactions[Long],
        "userid", RELATED ( Users[Id] ),
        "locationid", RELATED ( Locations[Id] )
    ),
    SELECTCOLUMNS (
        FILTER ( Tracker, Tracker[Event type] = "Register" ),
        "Table", "Register",
        "Created on", Tracker[Created On ],
        "Created on_Location", "",
        "event type", Tracker[Event type],
        "lat", Tracker[Lat],
        "long", Tracker[Long],
        "userid", RELATED ( Users[Id] ),
        "locationid", ""
    )
)
CahabaData's avatar
CahabaData
Memorable Member
9 years ago

Not sure you want or need a single table.  (sorry don't look at attachments...just general db feedback)

 

Ideally your User table should be Distinct - with no user repeating....and that should remain stand alone.

 

If there is NOT a 1:1 between Tracker/User  or NOT a 1:1 between Tracker/Transaction - then I'm not sure it would make sense to attempt to merge them into a single table.

 

Presuming there is a 1:1 between Location/Transaction - a Merge Query to put them together eliminates a table to simplify things a little.

 

Beyond that your join lines give you a virtual single table in a sense as you can relate things together.  A merge of multiple tables that do not have a 1:1 will result in alot of duplicate records and some confusing data.  The duplicates can be removed but if, for example, tracker fields don't directly relate to transaction fields - then they shouldn't be in the same record of the same table.