Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
somacdc
Contributor III
Contributor III

How to map last sorted date balance

Hi,

Please suggest the following requirement.

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

IDF_NUMBERNAV_1
201159/3/2032
2011510/3/2032
2011511/3/2032
2011512/3/2032

 

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

IDF_NUMBERNAV_2BALANCE
201158/3/20322400
201159/3/20322500
2011512/3/20322511

 

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

here is the desire result.

IDF_NUMBERNAVBALANCE
201159/3/20322500
2011510/3/20322500
2011511/3/20322500
2011512/3/20322511

 

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

2 Replies
sunny_talwar

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
Contributor III
Contributor III
Author

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...