topic dual SQL select to QlikView script in QlikView

dual SQL select to QlikView script

Wed, 20 Oct 2010 13:31:53 GMT

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

dual SQL select to QlikView script

syed_muzammil — Wed, 20 Oct 2010 13:54:16 GMT

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;

dual SQL select to QlikView script

Wed, 20 Oct 2010 13:57:50 GMT

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

dual SQL select to QlikView script

hector — Wed, 20 Oct 2010 14:04:32 GMT

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 )


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

dual SQL select to QlikView script

Anonymous — Wed, 20 Oct 2010 14:21:00 GMT

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;

dual SQL select to QlikView script

Thu, 21 Oct 2010 03:18:09 GMT

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