Dimension filtering - how?

[#1]

Hi,

I have an issue that I can't seem to figure out. Below is a straight table that I created in QV. I only want to be able to see the latested ID date in this report by each policy number and by each location number.

Policy Number	Location Num	Building Num	ID	Total Policy Gross Prem	Building Prem	Content Prem	Bus Inc Prem
				5,963	3,230	1,123	659
ABC	1	1	4/30/14	5,440	763	499	314
ABC	1	1	5/31/14	-1,419	723	473	298
ABC	1	2	4/30/14	57	57	0	0
ABC	1	2	5/31/14	54	54	0	0
ABC	2	1	4/30/14	467	390	77	0
ABC	2	1	5/31/14	443	369	74	0
ABC	2	2	4/30/14	390	365	0	25
ABC	2	2	5/31/14	366	344	0	22
ABC	2	3	4/30/14	84	84	0	0
ABC	2	3	5/31/14	81	81	0	0

I only want to see the following

Policy Number	Location Num	Building Num	ID	Total Policy Gross Prem	Building Prem	Content Prem	Bus Inc Prem
				-475	1,571	547	320
ABC	1	1	5/31/14	-1,419	723	473	298
ABC	1	2	5/31/14	54	54	0	0
ABC	2	1	5/31/14	443	369	74	0
ABC	2	2	5/31/14	366	344	0	22
ABC	2	3	5/31/14	81	81	0	0

I need to filter on the ID column to give me only the latest date by policy number, location number and by building number. In the above case, I only need 5/31/14, not 5/31/14 and 4/30/14.

Does anyone know?

Thanks

Tom

Does any

[maxgro]

a:
LOAD 
[Policy Number] & '-' & [Location Num] & '-' &  [Building Num] as NewField,
[Policy Number], [Location Num], [Building Num], 
ID, [Total Policy Gross Prem], [Building Prem], [Content Prem], [Bus Inc Prem]
FROM [http://community.qlik.com/thread/136998] (html, codepage is 1252, embedded labels, table is @1)
Where len(trim([Policy Number]))>0;

b:
NoConcatenate
load *
Resident a
where peek(NewField) <> NewField
order by [Policy Number], [Location Num], [Building Num], ID desc;

DROP Table a;

[#3]

Hi,

See the attached application for reference, hope it helps!!

Kiru

[#4]

Hi.

Thanks for the reply. I am almost there, but something I'm missing something.

There are many policies in this file. It works when I put in policy ID ABC, but not with any of the other policy ID's.

Also, I created the expression for NewField in the chart properties. Should I have done that or should that have happened in the Load data?

Thanks again for your help

Tom

Policy Number	Location Num	Building Num	ID	Total Policy Gross Prem	Building Prem	Content Prem	Bus Inc Prem	NewField
				5,963	3,230	1,123	659	PK2014RBT00170--
PK2014RBT00170	1	1	5/31/14	-1,419	723	473	298	PK2014RBT00170-1-1
PK2014RBT00170	1	1	4/30/14	5,440	763	499	314	PK2014RBT00170-1-1
PK2014RBT00170	1	2	4/30/14	57	57	0	0	PK2014RBT00170-1-2
PK2014RBT00170	1	2	5/31/14	54	54	0	0	PK2014RBT00170-1-2
PK2014RBT00170	2	1	4/30/14	467	390	77	0	PK2014RBT00170-2-1
PK2014RBT00170	2	1	5/31/14	443	369	74	0	PK2014RBT00170-2-1
PK2014RBT00170	2	2	5/31/14	366	344	0	22	PK2014RBT00170-2-2
PK2014RBT00170	2	2	4/30/14	390	365	0	25	PK2014RBT00170-2-2
PK2014RBT00170	2	3	4/30/14	84	84	0	0	PK2014RBT00170-2-3
PK2014RBT00170	2	3	5/31/14	81	81	0	0	PK2014RBT00170-2-3

[jagan]

Hi,

Try like this in script

Demo:

Load

PolicyNum,LocationNum,BuildingNum,Date(Date#(Date, 'M/D/YY')) AS Date,GrossPrem,BuildPrem,ContentPrem

FROM DataSource;

INNER JOIN (Demo)

LOAD

Date(Max(Date)) AS Date

RESIDENT Demo;

If you want to do this in front end

Demo:

Load

PolicyNum,LocationNum,BuildingNum,Date(Date#(Date, 'M/D/YY')) AS Date,GrossPrem,BuildPrem,ContentPrem

FROM DataSource;

INNER JOIN (Demo)

LOAD

Date(Max(Date)) AS Date,

1 AS Flag

RESIDENT Demo;

For BuildPrem : Sum({<Flag={1}>} BuildPrem)

Regards,

Jagan.

[maxgro]

I think the script works for every Policy Number if the requirement is to choose, for every Policy Number, Location Num, Building Num the last (by date) record. I changed a bit the script. Now the record to keep is just flagged (KeepRecordFlag). You can use this flag in the UI to filter rows.

In the load script you make the flag, in the UI, you use the flag   sum({$ <KeepRecordFlag={1}>}  somevalues)

If you think there is some problem with this, please post the expected result

a:
LOAD 
[Policy Number], [Location Num], [Building Num], 
ID, [Total Policy Gross Prem], [Building Prem], [Content Prem], [Bus Inc Prem], 
[Policy Number] & '-' & [Location Num] & '-' & [Building Num] as NewField
FROM [http://community.qlik.com/thread/136998] (html, codepage is 1252, embedded labels, table is @3)
Where len(trim([Policy Number]))>0;  
;

b:  
NoConcatenate load 
  *,
  if(peek(NewField) <> NewField,1,0)  as KeepRecordFlag  
Resident a  
//where peek(NewField) <> NewField  
order by [Policy Number], [Location Num], [Building Num], ID desc;  

DROP Table a;  

[#7]

Hi,

Thank you all for your help, but I think I am missing something.

The end result should look as follows.

Policy Number	Location Num	Building Num	ID	Total Policy Gross Prem	Building Prem	Content Prem	Bus Inc Prem	NewField
				475	-1,571	-547	-320	PK2014RBT00170--
PK2014RBT00170	1	1	5/31/14	-1,419	723	473	298	PK2014RBT00170-1-1
PK2014RBT00170	1	2	5/31/14	54	54	0	0	PK2014RBT00170-1-2
PK2014RBT00170	2	1	5/31/14	443	369	74	0	PK2014RBT00170-2-1
PK2014RBT00170	2	2	5/31/14	366	344	0	22	PK2014RBT00170-2-2
PK2014RBT00170	2	3	5/31/14	81	81	0	0	PK2014RBT00170-2-3

There are thousands of policies, but I only want the most recent by location and building.

I know I am close to a solution, but I am missing something.

Thanks

Tom

[jagan]

Hi,

Try like this

Temp:

LOAD  

[Policy Number], [Location Num], [Building Num],  

ID, [Total Policy Gross Prem], [Building Prem], [Content Prem], [Bus Inc Prem]

FROM [http://community.qlik.com/thread/136998] (html, codepage is 1252, embedded labels, table is @3) 

Where len(trim([Policy Number]))>0;   

;

Data:

LOAD

*,

If(Previous([Location Num]) <> [Location Num]  OR Previous([Building Num]) <> [Building Num], 1, 0) AS NewFlag

LOAD

*

RESIDENT Temp

ORDER BY [Location Num], [Building Num], ID desc;

Now in chart use this

Sum({<NewFlag ={1}>} SomeMeasure)

Regards,

Jagan.

[petter]

It could be accomplished without resorting to changes to the Load Script (Data Model) although a Load Script
change might in many circumstances be better....