topic Re: Sales Flag in script in QlikView

Sales Flag in script

satishkurra — Thu, 10 Mar 2016 20:25:50 GMT

I'm trying to create a flag for Sales > 0 and < 0 as below in the script.

But it is displaying as Invalid Expression on reload.

Can someone help me what went wrong?

LOAD

ID,

Sales,

If(Sum(Sales) > 0, 1,0) as SalesFlag;

SQL SELECT * FROM ......

Re: Sales Flag in script

sunny_talwar — Thu, 10 Mar 2016 20:28:32 GMT

Try this:

Table:

LOAD ID,

Sales;

SQL SELECT * FROM ......

Left Join (Table)

LOAD ID,

If(Sum(Sales) > 0, 1, 0) as SalesFlag

Resident Table

Group By ID;

Re: Sales Flag in script

Colin-Albert — Thu, 10 Mar 2016 20:32:22 GMT

To use an aggregation expression in your load script, you must include a group by clause.

sunindia shows how you can do this.

Re: Sales Flag in script

satishkurra — Thu, 10 Mar 2016 20:37:12 GMT

Can we include SET ANALYSIS in above Sum(Sales) in script window?

Also the above is still throwing error

Re: Sales Flag in script

satishkurra — Thu, 10 Mar 2016 20:41:14 GMT

Seems like i have the SQL statement and it is not supporting to use it.

I will use Preceding Load before this and see if i can achieve this?

Re: Sales Flag in script

satishkurra — Thu, 10 Mar 2016 21:23:16 GMT

I tried different way, it is not working, Can someone help me to achieve this using SET expression in script window?

Re: Sales Flag in script

satishkurra — Thu, 10 Mar 2016 21:23:34 GMT

I tried different way, it is not working, Can someone help me to achieve this using SET expression in script window?

Re: Sales Flag in script

sunny_talwar — Thu, 10 Mar 2016 21:25:28 GMT

There is not set analysis in the script. You can use ifs or Where statements to restrict things. Do you know what you are restricting?

Re: Sales Flag in script

satishkurra — Thu, 10 Mar 2016 22:06:52 GMT

I'm basically restricting the sales <0 and > 0

Re: Sales Flag in script

MarcoWedel — Thu, 10 Mar 2016 22:43:30 GMT

In case you only want a flag per individual sales value / row of your source table, one solution might be:

LOAD ID,
        Sales,
        Sign(Sales) as SalesFlag;
SQL SELECT * FROM ......

LOAD ID,
          Sales,
          If(Sales, 1, 0) as SalesFlag;
SQL SELECT * FROM ......

hope this helps

regards

Marco