PC Encryption Dashboard – Part 2

After loading and cleaning up data.

3. Extract Information

In this step, we’ll extract what we want to know from these 02 data tables. These are the questions:

  1. Which PCs have been fixed from 4 Aug to 12 Aug?
  2. Which PCs have not been fixed during that time? (Backlog PCs)
  3. Which PCs are newly reported in 12 Aug?

3.1 Which PCs have been fixed from 4 Aug to 12 Aug?

Those PCs are in the 4Aug table, but are not in 12Aug. We’ll join the 4 Aug and 12 Aug tables with Asset ID as the matching table, the result will be returned in a new table, with the rows that have Asset ID only in the 4Aug table.

Go to Edit Queries, select 4Aug table and click Merge Queries > Merge Queries as New

13_Merge New

The Merge dialog should be selected as below:

14_Fixed PC Merge

Click OK, then we’ll have a new table in the right hand panel, just rename the “Merge1” to “FixedPCs”.

14_Fixed PC Table

3.2 Which PCs have not been fixed during that time?

Those PCs appear in both 12Aug and 4Aug tables. We’ll join the 12 Aug and 4 Aug tables with Asset ID as the matching table, the result will be returned in a new table, with the rows that have Asset ID matches both 12Aug and 4Aug tables.

Go to Edit Queries, select 12Aug table and click Merge Queries > Merge Queries as New. The Merge dialog should be selected as below:

15_NotFixed PC Merge

Click OK, then we’ll have a new table in the right hand panel, just rename it to “NotFixedPCs”.

15_NotFixed PC Table

3.3 Which PCs are newly reported in 12 Aug?

Those PCs appear in only the 12Aug table, not in 4Aug table. We’ll join the 12 Aug and 4 Aug tables with Asset ID as the matching table, the result will be returned in a new table, with the rows that have Asset ID matches only 12Aug table.

Go to Edit Queries, select 12Aug table and click Merge Queries > Merge Queries as New. The Merge dialog should be selected as below:

16_New PC Merge

Click OK, then we’ll have a new table in the right hand panel, just rename it to “NewPCs”.

16_New PC Table

Now we have extracted all the information we need to know from the original 2 data sets. Let’s go to the next step to build a beautiful and neat dashboard to show the information up.


PC Encryption Dashboard – Part 1

PC Encryption Dashboard – Part 3

About dongthao

"Man does not simply exist but always decides what his existence will be, what he will become the next moment"
This entry was posted in Data Science, Software Tutorial and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s