QlikView - Simplifying/Flattening Input data

[#1]

Hey all,

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?

Sample Input could be

account,date,amount

12345,20010101,100.00

12345,20020101,100.00

12345,20030101,200.00

23456,20030505,000.00

23456,20060112,050.00

34567,19990101,999.99

45678,20091212,123.45

45678,20091230,100.00

Sample final QlikView table

account,date,amount

12345,20030101,200.00

23456,20060112,050.00

34567,19990101,999.99

45678,20091230,100.00

Thanks for any tips.

[#2 · Accepted solution]

Hi

See attached file for one way of doing it.

/Fredrik

[#3]

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?

[#4]

Thanks Fredrik!

I'll take a peek at your example now 🙂

[#5]

Wow, that's much easier 🙂

Thanks Fredrik, that'll definately work.