dual SQL select to QlikView script

[#1]

Hi guys,

I have a SQL statement:

select due_date, account_no, id
from aging where due_date = (select distinct max(due_date) fromaging );

My question is, how can I convert this SQL to QlikView script?

Hope that you can help me solve my problem.. Thanks in advance!

Regards,

Lester

[syed_muzammil]

Hi,

Have a look at this example below.

Do this same with your Aging table.

No need to write any where condition in SQL.

Tab:
LOAD * INLINE [
STOCK_NAME ;DATE ; PRICE; Month
PETR4 ;02/01/2010 ; 200; Jan
PETR4 ;03/01/2010 ; 210; Jun
USIM5 ;02/01/2010 ; 30; Mar
USIM5 ;03/01/2010 ; 40; Apr
] (DELIMITER IS ';');

right join

LOAD date(max(DATE)) as DATE
Resident Tab;

[#3]

Hi Lester,

why want you do that? Can you tell some backgrounds ? For my opinion, I would leave the filter-work in the source-database. Otherwise your QV may recieve tons of records and has to throw them away because the filter (in QV) doesn't match.

regards

Roland

[hector]

Hi you can use this way

Load
due_date, account_no, id
;
select due_date, account_no, id
from aging where due_date = (select distinct max(due_date) fromaging )

OR

t1:
select due_date, account_no, id from aging;
t2:
right (keep OR join)
Load
max(due_date) as due_date
resident t1;

I'm not sure about if it's only max(due_date) in the 2nd method, maybe it's account_no too?? if this is corrrect you will need a group by clause

Rgds

[Anonymous]

One more:

tmp:
SQL SELECT max(due_date) as due_date FROM aging;
result:
LOAD
 due_date,
 account_no,
 id
WHERE exists(due_date)
;
SQL SELECT
 due_date,
 account_no,
 id
FROM aging;
DROP TABLE tmp;

[#6]

Hi guys!

Thanks for your immediate replies.. I will test each of them and comment back which works for me. But the reason why I'm asking for this solution (asked by Roland) is that, I need to get the last payment for each Account that will be later used in my Aging process.. Please see the attached .qvw and you might help me with my problem. I have a "header" and "transaction" tables. I need to include the last Paid amount in header in order to check if its a full payment or partial payment and then process it to check its Aging. The report is somehow different from other Aging report because its the requirement of our client.

I hope you can help me (with my real problem)..

Thanks in advance! I really appreciate your help guys..

Lester