Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

dawgfather
New Contributor III

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

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)

ABC123Old1old_servers11/2/201611/2/2016-Not moved (no)
DEF123Old2old_servers11/4/201611/4/2016-Moved (yes)
DEF123New5new_servers11/6/2016-11/6/2016Moved (yes)
ABC345Old1old_servers11/5/201611/5/2016-Moved (yes)
ABC345New6new_servers11/6/2016-11/6/2016Moved (yes)
DEF456Old3old_servers11/5/201611/5/2016-Moved (yes)
DEF456New7new_servers11/8/2016-11/8/2016Moved (yes)
DEF678New8new_servers11/8/2016-11/8/2016Not moved (no)
ABC789New7new_servers11/9/2016-11/9/2016Not moved (no)
2 Replies
MVP & Luminary
MVP & Luminary

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

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
New Contributor III

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

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?