How to map last sorted date balance

[somacdc]

Hi,

Please suggest the following requirement.

I have following qvd (qvd1) with 4 different dates.

ID	F_NUMBER	NAV_1
2011	5	9/3/2032
2011	5	10/3/2032
2011	5	11/3/2032
2011	5	12/3/2032

and another qvd (qvd2) with 3 different dates and balance.

ID	F_NUMBER	NAV_2	BALANCE
2011	5	8/3/2032	2400
2011	5	9/3/2032	2500
2011	5	12/3/2032	2511

qvd1 check last sorted date balance in the qvd2. (e.g., balance of max(NAV_2) <= NAV_1)

here is the desire result.

ID	F_NUMBER	NAV	BALANCE
2011	5	9/3/2032	2500
2011	5	10/3/2032	2500
2011	5	11/3/2032	2500
2011	5	12/3/2032	2511

[sunny_talwar · Accepted solution]

May be this

Table1:
LOAD * INLINE [
    ID, F_NUMBER, NAV_2, BALANCE
    2011, 5, 8/3/2032, 2400
    2011, 5, 9/3/2032, 2500
    2011, 5, 12/3/2032, 2511
];

Table2:
LOAD *,
	 Date(If(ID = Previous(ID) and F_NUMBER = Previous(F_NUMBER), Peek('NAV_2')-1, '12/31/9999')) as NAV_2_End
Resident Table1
Order By ID, F_NUMBER, NAV_2 desc;

DROP Table Table1;

Table3:
LOAD * INLINE [
    ID, F_NUMBER, NAV_1
    2011, 5, 9/3/2032
    2011, 5, 10/3/2032
    2011, 5, 11/3/2032
    2011, 5, 12/3/2032
];

Inner Join (Table2)
IntervalMatch(NAV_1, ID, F_NUMBER)
LOAD NAV_2,
	 NAV_2_End,
	 ID,
	 F_NUMBER
Resident Table2;

Inner Join (Table2)
LOAD *
Resident Table3;

DROP Table Table3;

[somacdc]

Thanks for your kind reply.

you are requested to please reply the following also.

https://community.qlik.com/t5/QlikView-App-Development/Vertical-marquee-effect-in-pivot-table-and-gr...