topic Re: Set Analysis groups in QlikView

Set Analysis groups

pauldamen — Thu, 13 Mar 2014 08:12:55 GMT

All,

I have a formula which looks if a company had revenue in 2012 and not in 2013. In that case it should be called "Stopped", otherwise it is "Active". The set analysis to achieve this looks like:

if(sum({< Year = {'2012'}>} Revenue)>0 AND sum({< Year = {'2013'}>} Revenue)=0, 'Stopped', 'Active')

This formula works great in a table. But what I need is this to be loaded in the script, so I can use the Stopped field in other set analysis and in a listbox so the stopped companies can be excluded from selection.

But even pasting this formula in a listbox doesn't work, also the set analysis doesn't work in the script.

Any ideas?

Regards,

Paul

Re: Set Analysis groups

MK_QSL — Thu, 13 Mar 2014 08:19:17 GMT

Set Analysis is not supporting in SCRIPT...

You need to change your script something like below..

Temp:

Load

'Stopped' as Flag

ID,

Revenue

Resident TableName

Where Year = 2012 and Revenue > 0 or Year = 2013 and Revenue = 0 // or use Where Year = '2012' and Revenue > '0'

Join (YourFactTable)

Load * Resident Temp;

YourFactTableFinal:

Load *, If(Flag <> 'Stopped', 'Active') as Flag Resident YourFactTable;

Drop Tables YourFactTable, Temp;

Re: Set Analysis groups

pauldamen — Thu, 13 Mar 2014 08:32:34 GMT

Thanks for your help!

If I load the first temp table with the revenue and the year. Where do I load this from? My fact table or do I create a separate load from the source?

Regards,

Paul

Re: Set Analysis groups

Thu, 13 Mar 2014 08:42:03 GMT

Hi Paul,

Use a Variable like

Let Var=if(sum({< Year = {'2012'}>} Revenue)>0 AND sum({< Year = {'2013'}>} Revenue)=0, 'Stopped', 'Active')

and in your list box Expression

Write

If(not isNull('Filed') , var,0) then it will show only active status companies

Regards

Nani

Re: Set Analysis groups

pauldamen — Thu, 13 Mar 2014 08:46:03 GMT

Hi Nani,

It is not taking set analysis in a let function in the script.

Regards,

Paul

Re: Set Analysis groups

Thu, 13 Mar 2014 08:48:13 GMT

Create variable at settings ---> Variable Over view .

Re: Set Analysis groups

Thu, 13 Mar 2014 09:01:14 GMT

Hi Paul

Keep it simple in the script - load an if statement inside the sum instead of set analysis to return the 2012 /2013 revenue.

CompaniesData:

Load CompanyID,

if( sum(if(Year = '2012',Revenue,0)) >0 and sum(if(Year = '2013',Revenue,0)) =0,

'Stopped','Active')

from FactTable group by company;

Erica

Re: Set Analysis groups

Thu, 13 Mar 2014 09:02:17 GMT

Ps I would probably use the same approach as you - keeping it in the script. Depending on your data structure this will keep things quicker in the app

Re: Set Analysis groups

pauldamen — Thu, 13 Mar 2014 09:07:33 GMT

Hi Erica,

How can I make the script take SUM? As far as I know you can't put the sum statement in a script (I get an error which tells me it is an invalid expression)

Regards,

Paul

Re: Set Analysis groups

MK_QSL — Thu, 13 Mar 2014 09:32:24 GMT

Please check updated file...

Re: Set Analysis groups

Thu, 13 Mar 2014 09:52:41 GMT

You need to add a "group by" to the end of the load statement

Load A, sum(b) resident table group by A;;

Otherwise reply with your statement so we can see what the problem is?

Re: Set Analysis groups

pauldamen — Thu, 13 Mar 2014 10:03:39 GMT

Does it matter which field I use for the group by?

I do a group by company but it still gives an error, invalid expression.

See attached how that part of the script looks.

Re: Set Analysis groups

MK_QSL — Thu, 13 Mar 2014 10:06:11 GMT

Load Company,

SUM(YourField)

Resident YourTableName

Group By Company;

Re: Re: Set Analysis groups

pauldamen — Thu, 13 Mar 2014 10:08:02 GMT

That is exactly how I have it (little longer load script), see attached

Still returns the invalid expression

Re: Set Analysis groups

MK_QSL — Thu, 13 Mar 2014 10:14:54 GMT

Load CustomerID,

InvoiceNumber,

PartNumber

SUM(Sales)

Resident TableName

Group By CustomerID, InvoiceNumber, PartNumber;

Hope you get it now...

Re: Re: Set Analysis groups

pauldamen — Thu, 13 Mar 2014 10:17:02 GMT

I got it!!

When I do a second load only with the sum and the company and join that to the fact load it works.

Thanks both Manish and Erica for your quick help!

Regards,

Paul

Re: Set Analysis groups

Thu, 13 Mar 2014 10:19:28 GMT

It's because you need to group by the fields that do not have an aggregate expression (eg sum, avg, min...), otherwise qlikview does not know what to do with them.

Hence putting it in a separate, simpler table and then either joining it back on or keeping it as a lookup is the easiest way

Left join (facttable) Load company, if(sum(if(....)) and sum(if(..))).. as expression resident facttable

Erica

Re: Set Analysis groups

MK_QSL — Thu, 13 Mar 2014 10:49:37 GMT

It is difficult to group by 5 or 10 items. Better to load only 2-3 fields and group by in second table and join these two tables...