I'm looking for a way to remove a dimension (in this case time) of data within QlikView.
As a simplified example, if I'm pulling data from an external database that has an account number, date and an amount for each record and wanted my final QlikView table to have a unique account number and only the date and amount for the most recent transaction... how would I go about it?
I would normally pull the data in from the source first:
Some_Extract: LOAD SOURCEACCOUNT AS [ACCOUNT_temp], SOURCEDATE AS [DATE_temp], SOURCEAMOUNT AS [AMOUNT_temp] ; SQL SELECT * FROM SOMEDATASOURCE WHERE SOMECONDITIONAPPLIES
Then I ensure they're ordered... although not sure if this is necessary:
Some_Extract_ordered: LOAD [ACCOUNT_temp] AS [ACCOUNT_ordered], [DATE_temp] AS [DATE_ordered], [AMOUNT_temp] AS [AMOUNT_ordered] RESIDENT Some_Extract ORDER BY [ACCOUNT_temp] asc, [DATE_temp] desc ;
Then I assume you'd drop the records where the dates aren't the most current, but not sure how to do that... I assume you could pulled the data from "Some_Extract_ordered" into another table. Could you wrap an IF statement around each record to pull the ones you want and to drop the ones you don't?