topic Re: How to map last sorted date balance in QlikView

How to map last sorted date balance

somacdc — Fri, 20 Dec 2019 12:51:50 GMT

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

Re: How to map last sorted date balance

sunny_talwar — Fri, 20 Dec 2019 13:48:29 GMT

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;

Re: How to map last sorted date balance

somacdc — Fri, 20 Dec 2019 14:43:05 GMT

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-group-by-as-well/m-p/1656016#M448265