Mapped field for a qualifying condition - if something moved from A to B

[dawgfather]

I am having a hard time coming up with a query or condition where I can come up with a simple yes/no mapping as to whether the condition or event has taken place. I am hoping someone might be more resourceful than I am with QV.

Here’s the situation:

I have systems (laptops) connecting to one group of servers (“old_servers”) and corresponding database records of the last time they connected. I am trying to get them over to connect to a group of new servers (“new_servers”). Once they do connect to the “new_servers” group , I will have a record of the last date it connects. So the database will essentially show 2 records for a laptop connecting to two sets of servers at two different times/dates.

I can create a chart with laptop name, server name, last seen date, and then a group name (old_servers and new_servers) with mappings behind the scenes to define those list of servers to groups.

I have an expression defined for when the laptop was last seen connecting to the old_servers –

DATE(if(Database.ServerGroup = ‘old_servers’,Database.LastSeenDate,null()))

I have another expression similar to populate if connecting to new_servers –

DATE(if(Database.ServerGroup = ‘new_servers’,Database.LastSeenDate,null()))

If I see a laptop with two lines in the chart, one with a date for old_servers (say 11/4/16) and another line with a date for new_servers (say 11/6/16), then I know it has moved. Those I want to mark with a designation that they have moved (a “yes” in a yes/no field perhaps)

I am trying to come up with a mapping or qualifier that gives me a “Yes/No” field I can filter with. And I cannot seem to get there.

Yes = an (older) date connecting to old_servers AND a (newer) date connecting to new_servers.

No = only a date for that laptop in the old_servers grouping or only a date in the new_servers group, but not a date in both.

If I can get that date qualifier, great, but theoretically if it has an entry for both, then it will never go back, so the dates should always be older/newer – but you never know.

Any wizards out there?

Some sample data to work with:

Laptop field	Server field	ServerGroup field	LastSeenDate field	LastSeenOld expression	LastSeenNew expression	Desired outcome (comment)
ABC123	Old1	old_servers	11/2/2016	11/2/2016	-	Not moved (no)
DEF123	Old2	old_servers	11/4/2016	11/4/2016	-	Moved (yes)
DEF123	New5	new_servers	11/6/2016	-	11/6/2016	Moved (yes)
ABC345	Old1	old_servers	11/5/2016	11/5/2016	-	Moved (yes)
ABC345	New6	new_servers	11/6/2016	-	11/6/2016	Moved (yes)
DEF456	Old3	old_servers	11/5/2016	11/5/2016	-	Moved (yes)
DEF456	New7	new_servers	11/8/2016	-	11/8/2016	Moved (yes)
DEF678	New8	new_servers	11/8/2016	-	11/8/2016	Not moved (no)
ABC789	New7	new_servers	11/9/2016	-	11/9/2016	Not moved (no)

[marcus_sommer]

I think you could do a mapping like:

map:

mapping load distinct [Laptop field], 'Moved (yes)' as Result

From YourSource where [ServerGroup field] = 'new_servers';

table:

load

     *,

     applymap('map', [Laptop field], 'Not moved (no)') as [Desired outcome]

From YourSource;

- Marcus

[dawgfather]

Thank you for your feedback. I suppose I need to clarify the table.

The Desired Outcome is not an available field. It is merely a comment I made to the audience here so that if the mapping works, that is what I would expect to see. Merely having an entry connecting to the new_servers group does not constitute a "move". If a laptop never connected to an old_servers server, as in it is a new laptop and only connects to a new_servers server, then it is not a "move".

A "move" has to be where there was (is) an entry or record for a laptop connecting to an old_servers group, and then it connects to a new_servers group, only then has it actually "moved". Make sense?