topic Find missing data in latest month in QlikView

Find missing data in latest month

surajdhall — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I want to find whether any data is missing in latest month for the 2 dimensions(Project & Location). I am only interested in missing data which is listed in 'Location_Map' table.

In my Fact table Product C with Location UK has missing data. So i want to show this in a listbox.

My fact table also contain a flag to identify latest records.

Please help how to get the missing data list.

//Script & Data

Location_Map:
Mapping
LOAD * INLINE [
Product, Location
A, US
C, UK
];

Data:
LOAD *,
Date(MonthStart(Date),'MMM-YYYY') as MonthYear,
num(Date) as MonthNum
INLINE [
Product, Date, Margin, Sales, Location
A, 01/01/2017, 1184632, 4227592, US
A, 01/02/2017, 1273124, 4545040, US
A, 01/03/2017, 112483, 130487, US
B, 01/03/2017, 136175, 145455, UK
C, 01/02/2017, 11188205, 11361659, UK
];

maxMonth_map:
Mapping
LOAD max(MonthYear) as MaxMonth,
1 as MaxFlag
Resident Data;

Fact:
LOAD *,
ApplyMap('maxMonth_map',num(MonthYear),0) as MaxFlag
Resident Data;

DROP Table Data;

Re: Find missing data in latest month

chrismarlow — Mon, 18 Feb 2019 20:53:29 GMT

Hi,

I recommend you read this;

https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394

Cheers,

Chris.

Re: Find missing data in latest month

surajdhall — Tue, 19 Feb 2019 06:52:32 GMT

Thanks for your reply.
I don't want to generate missing data. I want to map the below mapping table to only those records in fact table that has missing latest month.

In the example flag only the highlighted record, as it has no March data for Product A and Location UK in the mapping table.

Location_Map:
Mapping
LOAD Product & '|' & Location & '|' & 1 as Key,
1 as Link
INLINE [
Product, Location
A, US
C, UK
];

I have created MissingData column, but it flagging wrong records. I only want it to product C & Location UK row.

Re: Find missing data in latest month

surajdhall — Tue, 19 Feb 2019 06:53:35 GMT

forgot to attach my updated app.

Re: Find missing data in latest month

chrismarlow — Tue, 19 Feb 2019 20:40:43 GMT

Hi,

My bad.

If you want a visualisation expression you could try;

If(IsNull(Only({<Key=E({<MaxFlag={1}>} Key)>}Key)),0,1)

Or scripting you can add a left join & then load to a new table;

left join (Fact)
Load 
Key,
1 as FoundTemp
resident Fact
where MaxFlag=1;

Fact2:
NoConcatenate
Load
	*,
	If(IsNull(FoundTemp),1,0) AS Found
Resident Fact;


DROP Tables Data, Fact;
drop field FoundTemp;

Cheers,

Chris.