Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
surajdhall
Contributor III
Contributor III

Find missing data in latest month

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;

4 Replies
chrismarlow
Specialist
Specialist

surajdhall
Contributor III
Contributor III
Author

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.

image1.png

surajdhall
Contributor III
Contributor III
Author

forgot to attach my updated app.

chrismarlow
Specialist
Specialist

Hi,

My bad.

If you want a visualisation expression you could try;

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

20190219_1.png

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.