topic Re: As on Date on RunTime in QlikView

As on Date on RunTime

Fri, 15 Jul 2011 10:29:21 GMT

Hi All,

I need your help for building a As on Date functionality for Few of my reports & i believe you all giuys can help me on the same.

Please see the attached Excel File as a Sample Data to be worked on.

In the attached excel you can see there is 1 single Receipt Number & have 3 Different Cash Rcpt ID's. (53000, 64001, 73000) now if i select a create a Autogenerated Date & select 17-Jul-2011 then i should search for all the GL Dates & give me the Max GL Date against each of the Cash Rcpt ID's & if the Status is 'REVERSED' for that specific row, then do not show the entire Cash Rcpt ID.

in this above case if i select 17-July-2011 then for

For Cash Rcpt ID :- 53000 max Date = 12-May-2011 & status <> 'REVERSED' hence it should get displayed

For Cash Rcpt ID :- 64001 Max Date = 07-Jul-2011 & Status = 'REVERSED' hence this whole Cash Rcpt ID (2 Rows) should not come in the report.

For Cash Rcpt ID :- 73000 Max Date = 04-Jul-2011 & Status <> 'REVERSED', hence it should come in the report.

2nd Scenario

If i select 6th-July-2011 (in As on Date filed Which is Autogenerated Date Filed but not the GL Date") -- Please Note 🙂

For Cash Rcpt ID :- 53000 max Date = 12-May-2011 & status <> 'REVERSED' hence it should get displayed

For Cash Rcpt ID :- 64001 Max Date = 20-Jan-2011 & Status <> 'REVERSED' hence this Record will come in the report for this as on Date (6th Jul-2011)

For Cash Rcpt ID :- 73000 Max Date = 04-Jul-2011 & Status <> 'REVERSED', hence it should come in the report.

Need all your help & suggestion..

Rgds,

Srini

Re: As on Date on RunTime

kaushiknsolanki — Fri, 15 Jul 2011 11:28:31 GMT

Hi,

Check the attached Document. Hope this is what you want.

Regards,

Kaushik Solanki

As on Date on RunTime

Fri, 15 Jul 2011 13:03:53 GMT

Hi Kaushik,

Thanks for your quick help..

If i give 06-jul-2011 in the input box it should display me the below records

Cash Rcpt ID, GL Date, status, RcptAmt

53000, 12-May-2011, Cleared, 1000 "Here GL Date is <= inputDate"

64001, 20-Jan-2011,cleared,132360 (only this records will come in report, As on input date 06-July-2011 the max date for this id is 20-Jan-2011 & not reversed)

73000,4-Jul-2011,cleared,85000 "Here GL Date is <= inputDate"

If 9 Give 08-Jul-2011 then below said records should come in the report.

Cash Rcpt ID, GL Date, status, RcptAmt

53000, 12-May-2011, Cleared, 1000 "Here GL Date is <= inputDate"

64001, 07-Jul-2011,cleared,132360 (As on input date 08-July-2011 the max date for this id is 07-Jul-2011 & reversed so this entire ID "64001" wont come in the report.)

73000,4-Jul-2011,cleared,85000 "Here GL Date is <= inputDate"

But i dont see if the input Box functionaly is working.

I need your help in bringing all the above said 2 scenarios in the one single Report.

please suggest!!!

Thanks once again for your quick help... 🙂

Rgds,

Srini

Re: As on Date on RunTime

kaushiknsolanki — Fri, 15 Jul 2011 13:14:46 GMT

Hi,

First confirm me that the excel sheet contains date in which format.

Is its a DD/MM/YYYY or MM/DD/YYYY

Regards,

Kaushik Solanki

As on Date on RunTime

Fri, 15 Jul 2011 13:18:38 GMT

Hi,

Its in DD/MM/YYYY

Rgds,

Srini

Re: As on Date on RunTime

kaushiknsolanki — Fri, 15 Jul 2011 13:27:56 GMT

Hi,

I have done some changes in the file, and uploading the same.

Now tell me with reference to this file. what you want.

Regards,

Kaushik Solanki

Re: As on Date on RunTime

Fri, 15 Jul 2011 14:00:24 GMT

Hi Kaushik,

M attaching the Qvd for your ref as i see there is a Problem with the Date "Please find attached the Qvd".

I think u have got very near just a further more small Help..

if i give 20-July2011 in input Box

then you should also compare the GL Date with the <= input Date &

i should give below

53000, 12-May-2011, 1000, CLEARED -- "This record should come as the input box Date "20-Jul-2011" is >= max(GL Date which is 12-May-2011").

64001, 07-July-2011, REVERSED -- "This Row should not come as for this ID "64001" the Max Date is 07-July-2011 & it has a Status 'REVERSED' then the entire ID '64001' should not come.

73000, 04-July-2011, CLEARED -- "This record should come as the max(GL Date) is <= input Box Date. & Status is 'CLEARED'.

----- "If in case i am giving 06-July-2011 in input box then i should get below records"

53000, 12-May-2011, 1000, CLEARED -- "This record should come as the input box Date "20-Jul-2011" is >= max(GL Date which is 12-May-2011").

64001, 20-Jan-2011, CLEARED -- "This Row should come as for this ID "64001" the Max Date is 20-Jan-2011 & it has a Status 'CLEARED' then the record should come.

73000, 04-July-2011, CLEARED -- "This record should come as the max(GL Date) is <= input Box Date. & Status is 'CLEARED'.

please let me know if i have missed any ..

Thanks for your help & time.. 🙂

Rgds,

Srini

Re: As on Date on RunTime

Sat, 16 Jul 2011 08:31:08 GMT

Dear Srini,

Please Find the Attached Appln.

Hope this is what u want as out put .

Re: As on Date on RunTime

Sat, 16 Jul 2011 13:09:46 GMT

Hi Kaushik,

No, if i m giving date as 06-July-2011' i.e 06/07/2011 then it should also show me the 64001 record with date as 20-Jan-2011 & Cleared as status but if i give 07/jul/2011 then this entoire Receipt ID 64001 should not come but the other 2 should come as for 64001 it has a latest status as on 7/Jul/2011 is 'Reversed'.

Hope now its clear.. 🙂

Rgds,

Srini

Re: As on Date on RunTime

Mon, 18 Jul 2011 04:29:02 GMT

Dear Srini,

I guess the Above Attached Test.qvw appln fulfils ur requirement,

please check

Re: As on Date on RunTime

kaushiknsolanki — Mon, 18 Jul 2011 06:14:36 GMT

HI,

As we discussed, here is what i have developed.

This works but the problem is it is developed using if statement, which is not good for large volume of data.

I am also working on solving this using set analysis.

Have a look at the attached file and do respond me, is this what you want?

Regards,

Kaushik Solanki

As on Date on RunTime

Mon, 18 Jul 2011 10:49:56 GMT

Hi Bhaskar & Kaushik,

Thanks for your Help Guys... 🙂 KUDO's..

I have used a Different Logic to arrive at the same using Aggr func()..& yes i will try out the best options as suggested by you both guys as per the Data Size..

Rgds,

Srini