topic Re: Create flag field in script in QlikView

Create flag field in script

Tue, 12 Jul 2011 21:44:13 GMT

I need to create a new field called CommissionFlag to indentify customers who have commission over a time period vs those who don't.

I've tried the below syntax but get an Invalid expression error:

Load

Gross,

if(sum(Gross)>0,'Y','N') as Gross Flag

From...

I've also tried this, but I get the "Execution of script failed. Reload old data?" box:

Load

Gross,

if(Gross=0,'N','Y') as Gross Flag

From...

Any suggestions on how to create the flag/field?

Re: Create flag field in script

Tue, 12 Jul 2011 22:16:17 GMT

Hi,

Looks like writing the field name "Gross Flag" is incorrect. Just do not separate the words: GrossFlag.

This script is working for me with loading from Excel:

LOAD ID,
FieldA,
if(ID = 5, 'Y', 'N') as GrossFlag
FROM
[..\Fields.xlsx]

(ooxml, embedded labels);

regards,

wojciechg

Re: Create flag field in script

johnw — Tue, 12 Jul 2011 22:28:47 GMT

Or enclose it in double quotes or brackets. The sum() probably didn't work because you weren't doing a group by. It sounds like you don't need a group by, though, and can just get rid of the sum().

if(Gross=0,'N','Y') as "Gross Flag"

Re: Create flag field in script

stevedark — Tue, 12 Jul 2011 22:29:11 GMT

Hi there,

The space in the Gross Flag field name is acceptable if you simply enclose in square brackets, eg. [Gross Flag].

The first statement would have failed as you had a Sum statement without a Group By clause. If you sum one column you need to sub all the others also. This is usually not a good thing as removing detail means that QlikView can not do as much in depth analysis.

The second statement will work fine, but will return 'Y' even if the values in the Gross field net out to zero (eg, 20, -5, -15). I don't know if this will cause an issue? If so you will either need to aggregate all your data or have two tables in your data model - one with the detail and the other with the flag in it - where the flag is on the aggregated values.

Hope that helps,

Steve