topic Re: Picking the first option in QlikView

Picking the first option

Mon, 11 Nov 2013 21:35:03 GMT

I am having issues trying to pick the first option depending on the different picks. I have three different picks. "Customer_Pick", "Company_Pick", and "Default_Pick". I need to pick up the first item that is a "Customer_Pick". If there is no "Customer_Pick", I need to pick up the first item that is a "Company_Pick". If there are no "Customer_Pick" or "Company_Pick", I need to grab the first "Default_Pick" item.

Re: Picking the first option

Mon, 11 Nov 2013 21:41:07 GMT

Please try Alt() function..

Re: Picking the first option

jonasheisterkam — Mon, 11 Nov 2013 21:43:33 GMT

Load with rowno() flag with 1 if it is pick then sort by Custom-> Company -> Default -> rowno and Group By if you need the pick not for total. If you group the aggregation is firstvalue(), if you only want a Var with total first value of a field you can after sort use let a=peek('Field',0,'Table')

Re: Picking the first option

Mon, 11 Nov 2013 22:59:00 GMT

Please try like below:

LOAD Items,

Customer_Picks,

Company_Picks,

Default_Picks,

IF(NOT IsNull(Customer_Picks), 'Customer' , IF(Not IsNull(Company_Picks), 'Company' ,'Default') ) AS Pick,

Serial_Code

FROM Test.xlsx (ooxml, embedded labels, table is Sheet1);

find the attached file for reference.