missing date and missing id highlited

[#1]

in attach excel you get three sheets . each sheet has data daily basis for three different id . my requirement is i have to make a report in ui where i point out that which unit is missing in which date.like ID 1 is missing in 06/01/2015 and ID 2 is misssing in 08/01/2015 and 13/01/2015 and ID 3 is missing 03/01/2015 and 02/01/2015.

[#2]

sunindia‌gwassenaar‌jaganmohanrao‌manishkharsh‌maxgro‌Anandchary‌tresesco‌swuehl‌MarcoWedel‌  please help me how can i do this.

[swuehl · Accepted solution]

You could add the missing combinations for DATE and ID in the script, like

INPUT:
LOAD ID,
     DATE,
     VALUE
FROM
[test (4).xlsx]
(ooxml, embedded labels, table is T);


LOAD ID,
     DATE,
     VALUE
FROM
[test (4).xlsx]
(ooxml, embedded labels, table is U);


LOAD ID,
     DATE,
     VALUE
FROM
[test (4).xlsx]
(ooxml, embedded labels, table is Y);

TMP:
LOAD DISTINCT ID Resident INPUT;

JOIN
LOAD DISTINCT DATE RESIDENT INPUT;


LEFT JOIN (TMP)
LOAD * RESIDENT INPUT;


DROP TABLE INPUT;

Then create a chart with dimensions ID and DATE and expression

=NullCount(VALUE)

ID	DATE	NullCount(VALUE)
		6
1	06.01.2015	1
2	08.01.2015	1
2	13.01.2015	1
3	02.01.2015	1
3	03.01.2015	1
3	11.01.2015	1

[#4]

its working thank you

[#5]

@swuehl

this answer is worked but suppose one day(02.01.2015) is missing for all ID s(1,2,3) how can I highlighted  those units also. please reply.

[swuehl]

Instead of this line

JOIN 

LOAD DISTINCT DATE RESIDENT INPUT; 

which reads in the dates from your INPUT table,

use a date field that holds all dates you are interested in, basically a master calendar:

The Master Calendar