topic Script help in QlikView

Script help

avastani — Fri, 23 Oct 2009 07:35:38 GMT

I have a table where there are multiple addresses for each company but I want to extract records for only those addresses that were added last. I know they were added last by the LastUpdateDate field. How can I do that? Subqueries is not an option as QV doesn't permit that and a for loop would take forever in a table with 2m+ records. Is there any other option.

Thanks in advance.

Script help

johnw — Fri, 23 Oct 2009 07:48:51 GMT

If you're reading the table with SQL, I don't see why subqueries or some other approach on the database side wouldn't work, but you can also handle it in QlikView. There is almost certainly a more efficient method, but this seems straightforward enough:

[Companies]:
LOAD
"Company ID"
,"Last Update Date"
,"Company Address"
FROM ...
;
INNER JOIN ([Companies])
LOAD
"Company ID"
,max("Last Update Date") as "Last Update Date"
RESIDENT [Companies]
GROUP BY "Company ID"
;
DROP FIELD "Last Update Date"
;

Script help

avastani — Fri, 23 Oct 2009 07:54:08 GMT

Thanks John. My brain was so fried, I couldn't think straight. I have another datamodel problem in another thread if you could please help take a look, I would appreciate it. Thanks again.