How to identify missing ID

[Anonymous]

How do I find missing devices during month scans: Image 1.

Each devices are scanned every month. If a device ID does not have any of the 4 Run Dates (8/1/2016, 9/1/2016 ,10/1/2016, 11/1/2016) it should be flagged with an additional field. As shown in the image2.

For Eg, maple2 is missing on 10/1/2016 & 11/1/2016. Therefore fields Miss_month3 and Miss_month4 have values ‘Flag’. Similarly, maple5 have data only for 11/1/2016, so Miss_month1, Miss_month2, Miss_month3 gets the value ‘Flag’.

I am looking to build if statements that will help me create Flag fields as above to filter out missing Device IDs any help would be awesome. Many Thanks.

[krishna_2644]

Please provide sample data so that we play around and come up with some solution,.

Thanks

[Anonymous]

Attached are excel files and qvw. Basically I want to create a calculated field in the script, say "Flag Field", and it will have 4 values:

1) Missing on 8/1/2016

2) Missing on 9/1/2016

3) Missing on 10/1/2016

4) Missing on 11/1/2016

when I select 'Missing on 8/1/2016' from the list box it should pull rows with Device ID - maple5

when I select 'Missing on 10/1/2016' from the list box it should pull rows with Device ID - maple2, , & maple5

In general I am trying to identify Device IDs that are missing from any Run Dates.

I am using min, max and count of rows but no luck so far. Any help would be great.

Thanks.

[Anonymous]

sunny‌

[vinieme12]

You will need to create a flag for each device for each date; check the attached app

[Anonymous]

Vineeth,

Thank you for posting your reply. I am still working on it to get to where I need to be. and its getting closer.

In my actual app, i ma maintaining a count Metrics (text box) where i display below numbers:

for 10/1/2016:

Returning Ids = 3 (because maple 1,3, & 5 existed in previous month)

Missing Ids = 1(maple2)

New Ids = 0.

to do this i am exporting your pivot table into excel and feeding it into qv where use set analysis to count above metrics. so yes this is a round about but is helpful. I am trying to do it in the same qvw. if u can think of something better. let me know.

Regards

[vinieme12]

Don't need to load the pivot as excel again, just use set analysis now that we have a flag.

Just create a yearmonth field with the execution date field from the flag table like 201601,...,201612

VPrevMonth= num#(year(today()&num(month(today()),'00'))

Something like

Count ( {<    flag = {'executed'},  yearmonth = {'$(=vPrevMonth)'}

>}distinct Deviceid)