Expression formula based on a column

[#1]

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.

[Clever_Anjos]

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$);

[antoniotiman · Accepted solution]

Try

LOAD

.......

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

Regards,

Antonio

[#4]

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!

[Clever_Anjos]

You don´t need a resident one.

You can read from your excel as my example

[antoniotiman]

This loads directly from Excel File.

[#7]

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.

[#8]

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$);

[Clever_Anjos]

No problem

Good to read that you solve your problem.

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

[Clever_Anjos]

thanks