topic Re: missing date and missing id highlited in QlikView

missing date and missing id highlited

Sat, 05 Dec 2015 13:30:29 GMT

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.

Re: missing date and missing id highlited

Sat, 05 Dec 2015 15:00:30 GMT

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

Re: missing date and missing id highlited

swuehl — Sat, 05 Dec 2015 15:07:42 GMT

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

Re: missing date and missing id highlited

Sat, 05 Dec 2015 15:21:31 GMT

its working thank you

Re: missing date and missing id highlited

Wed, 25 May 2016 14:44:36 GMT

@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.

Re: missing date and missing id highlited

swuehl — Wed, 25 May 2016 15:28:41 GMT

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