Find missing data in latest month

[surajdhall]

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;

[chrismarlow]

Hi,

I recommend you read this;

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

Cheers,

Chris.

[surajdhall]

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.

[surajdhall]

forgot to attach my updated app.

[chrismarlow]

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.