topic If and Above function - Not getting all records in QlikView

If and Above function - Not getting all records

Anonymous — Mon, 11 Feb 2013 23:35:39 GMT

In the table below I have sorted on "CustAccount" then "Purchase date" and finally "Bought Package". I'm aiming at exporting a list to Excel, which will show me the latest purchase of a customer and if he has bought a "package". My idea was then to create a calculated with an IF function so I quickly could delete unwanted lines in Excel

I used this function to create the calculated field "Delete Row":

if (Above(Column(1))=Column(1), 'Yes', 'No')

But it is not working the way I wanted. If that were the case, there would be a "yes" at the second 10000066 customer and several others. It seems to me that if the purchase date is different then my function fails.

Any ideas on what I'm doing wrong 🙂

Re: If and Above function - Not getting all records

swuehl — Tue, 12 Feb 2013 00:10:15 GMT

Not 100% sure what you are looking for, maybe

=if(CUSTACCOUNT = above(CUSTACCOUNT),'Yes','No')

Or try creating a chart with only dimension CUSTACCOUNT, and use an expression with FirstSortedValue() function to return the value for Bought Package for the latest purchase date

=FirstSortedValue([Bought Package], -[Purchase Date])

[this requires that you only got one Bought Package status per unique purchase date].

Regards,

Stefan

Re: If and Above function - Not getting all records

Tue, 12 Feb 2013 00:12:02 GMT

Column(1) would be your PurchaseDate column, assuming it's the first expression.

That said, instead of exporting this table with such a 'flag' so you must then manually delete, why not just construct the chart/table so that you export just one row per customer, with latest purcahse date and a flag whether that customer ever bought a package. Something like this:

Code is something like this (see attached QVW):

Fact:
Load 
  *,
  If(BoughtPackage='No',Dual('No',0),Dual('Yes',1)) As BoughtPackageFlag;
LOAD CustomerAcct, 
  date(floor(PurchaseDate)) As PurchaseDate, 
  IF(IsNull(BoughtPackage),'No',BoughtPackage) As BoughtPackage
FROM
LatestPurchase.xlsx
(ooxml, embedded labels, table is Sheet1);