topic Re: Expression formula based on a column in QlikView

Expression formula based on a column

Wed, 27 Jul 2016 15:20:59 GMT

Hi Experts,

The attached Excel file (generated from QV app) explains my problem.

I need a formula to evaluate the FLAG expression based on the CURR and PREV fields. The specs are as follows.

(1) If PREV is null, FLAG is 'N'.

(2) If PREV has a value that matches any value above that row in the CURR column, FLAG = 'Y'.

(2) If PREV has a value that does not match any value above that row in the CURR column, FLAG = 'N'.

Appreciate your help.

Re: Expression formula based on a column

Clever_Anjos — Wed, 27 Jul 2016 16:15:59 GMT

Maybe this?

LOAD POSKI,

PLTAG,

VORNR,

CURR,

PREV,

FLAG,

If(IsNull(Peek('PREV')),'N',

iF(Exists(CURR,PREV),'Y','N')) AS FLAG2

FROM

[https://community.qlik.com/servlet/JiveServlet/download/1092965-238370/QVTEST.xls]

(biff, embedded labels, table is Sheet1$);

Re: Expression formula based on a column

antoniotiman — Wed, 27 Jul 2016 16:17:25 GMT

Try

LOAD

.......

If(Len(Trim(PREV)) = 0,'N',If(Exists(CURR,PREV),'Y','N')) as FLAG
FROM ......;

Regards,

Antonio

Re: Expression formula based on a column

Wed, 27 Jul 2016 16:26:48 GMT

I was hoping for a formula without having to load from a resident table. May be there isn't one and this is the best option. Thank you both Antonio and Clever Anjos!

Re: Expression formula based on a column

Clever_Anjos — Wed, 27 Jul 2016 16:36:48 GMT

You don´t need a resident one.

You can read from your excel as my example

Re: Expression formula based on a column

antoniotiman — Wed, 27 Jul 2016 16:43:39 GMT

This loads directly from Excel File.

Re: Expression formula based on a column

Wed, 27 Jul 2016 16:50:59 GMT

Thanks Anjos! I know that a resident load is not required in this simple case but the Excel file is only a small part of my QV dashboard. I attached the Excel file merely to illustrate the problem. If there is no formula for the expression, I need to use RESIDENT LOAD in my dashboard. I find Antonio's suggestion to check for Len(Trim(PREV)) = 0 works. Your idea of IsNull(Peek('PREV')) is elaborate and I had bad experiences using IsNull() function.

Re: Expression formula based on a column

Wed, 27 Jul 2016 17:00:27 GMT

Addendum - check this code that uses IsNull() function:

ZZPSNW2:
LOAD POSKI, PLTAG, VORNR, CURR, PREV, FLAG,
If(Len(Trim(PREV)) = 0,'N',If(Exists(CURR,PREV),'Y','N')) as FLAG1, //This works in all cases
If(IsNull(Peek('PREV')),'N',If(Exists(CURR,PREV),'Y','N')) AS FLAG2 //This does not
FROM [..\..\QVDloadfiles\QVTEST.xls]
(biff, embedded labels, table is Sheet1$);

Re: Expression formula based on a column

Clever_Anjos — Wed, 27 Jul 2016 17:23:58 GMT

No problem

Good to read that you solve your problem.

If you like my answer, would you mind marking it as "Helpful"?

Re: Expression formula based on a column

Clever_Anjos — Wed, 27 Jul 2016 17:33:06 GMT

thanks