Count based on max date in report containing historical data

[CreepyCatLady]

Hello.

I need to create a field that counts HOST_NAME per user, but only for the current date (meaning, I don't want to see machines a person had in the past, only the ones the user has now).

I can do this in a simple query using the following script:

VIEW_NAME:

 LOAD max(ASOFDATE) as MaxDate,

     "HOST_NAME",

     "USER" 

Group by "HOST_NAME", "USER"

;

SQL SELECT *

 FROM DB."VIEW_NAME"

  ;

HOST_COUNT:

 Load

              USER,

              count (DISTINCT (HOST_NAME)) as HOST_COUNT

              resident [DB_NAME]

    Group by USER;

The larger report that this logic needs to be added to contains historical data; when I tried to recreate this in that report by creating a resident table with only the MaxDate and creating the HOST_COUNT table from that, it did not work. HOST_COUNT continued to see results from all ASOFDATE dates.

I need the historical data for the rest of my report; so I can’t just load only max(ASOFDATE) for the entire report. How do I get around this?

[edwin]

the solution would really depend on the DM.  what i would do is build a bridge that associates each date to the last device per host prior or equal to the date.  therefore, when a single dtae is selected, it is associated to the most current record before or = to that date:

Date	host	Device	AsOfDate
12/31/2022	A	D1	12/31/2022
12/31/2022	B	D2	12/1/2022
12/31/2022	C	D4	12/31/2022
12/30/2022	A	D3	12/28/2022
12/30/2022	B	D2	12/1/2022
12/29/2022	A	D3	12/28/2022
12/29/2022	B	D2	12/1/2022

fact:

host	Device	AsOfDate	Key = host + AsOfDate
A	D1	12/31/2022	A | 12/31/2022
B	D2	12/1/2022	B | 12/1/2022
C	D4	12/31/2022	C | 12/31/2022
A	D3	12/28/2022	A | 12/28/2022

this is the bridge:

Date	Key = host + AsOfDate
12/31/2022	A | 12/31/2022
12/31/2022	B | 12/1/2022
12/31/2022	C | 12/31/2022
12/30/2022	A | 12/28/2022
12/30/2022	B | 12/1/2022
12/30/2022	A | 12/28/2022

for each calendar date, you associate the appropriate fact record with the as of date for each host.

so you can simply count the host per date

[CreepyCatLady]

This sounds like a great solution, but I have no idea how to go about doing it. Can you point me to some tutorials or help guides for the process?

Thank you for the quick response!

[edwin]

Post your data model. Or at the least the tables where your host devices and as of date and your calendar

sample data would help as well

[CreepyCatLady]

This is the data model that gets me all the historical counts. I need HOST_COUNT to only show current date data.

[edwin]

  if a user can only have 1 host, then the as of date is enough as the next as of date will have a different host hence the start and end dates can be determined. 

if a user can have multiple hosts, then shouldnt there be a start and end date?

can you pls add sample data and how this will be reported?

[CreepyCatLady]

Ok here is what is happening as-is. Note that "HOST NAME" in this table is 

=if(Date(ASOFDATE,'DD-MM-YYYY')='$(vMaxDate)',HOST_NAME)

This limits the table results to current date. The HOST_COUNT field is from the load script, so it is seeing all historical host-to-user associations. You can see that the count field is reporting the correct number of hosts because the table is sorted by user and you can see how many times the user name shows up.

[edwin]

is it possible to share the data for this specific user to understand how the count would vary according to time?

what is the data for this specific user

[CreepyCatLady]

It contains historical data going back to 2019, but I can show you a snippet. There are multiple results for each machine because there is an ASOFDATE for every time the machine is scanned. But the HOST_COUNT field is only counting distinct machine names. You'll see here that the table formula is also counting 5 machines because I do not have this data filtered for current date.