topic Using conditions in the script to create groups in QlikView

Using conditions in the script to create groups

mrchristopher — Mon, 20 Jun 2011 14:41:24 GMT

I'm trying to create a new field within the script so that I can group data within Qlikview.

The condition is based on the 1st letter of the field 'Product Class' e.g. All product classes equal to 'A' = 'Group A' and everything else = 'Other'

Here is what I have so far:

ODBC CONNECT TO SysproCompanyA;

SQL SELECT Area,

CostValue,

Customer,

CustomerClass,

NetSalesValue,

ProductClass,

QtyInvoiced,

Salesperson,

StockCode,

TrnMonth,

TrnYear,

FROM SysproCompanyA.dbo.ArTrnDetail;

SELECT ProductClass, (Left(ProductClass,1)) AS Sector FROM SysproCompanyA.dbo.ArTrnDetail;

I've tried various If statments but I can't get any to work. Can you help?

BTW this is my 1st day of looking at any SQL scripting, so go easy.

Using conditions in the script to create groups

kaushiknsolanki — Mon, 20 Jun 2011 15:02:04 GMT

Hi,

Instead of creating group in script you can create groups in document.

Click on Settings-> Document Properties -> Groups

Here you can create groups as per you want.

Hope this will help you.

Regards,

Kaushik Solanki

Using conditions in the script to create groups

mrchristopher — Mon, 20 Jun 2011 15:12:29 GMT

Excellent soloution, Thank You.

Re: Using conditions in the script to create groups

Miguel_Angel_Baeyens — Mon, 20 Jun 2011 15:12:51 GMT

Hello Kaushik,

That Groups tab is used to create drill down groups and cyclic groups based on existing fields (or even using expressions) but it doesn't seem to be the case.

Use the following code instead

ODBC CONNECT TO SysproCompanyA;
Table: // table label, not mandatory but very useful
LOAD Area, // the LOAD part is not mandatory either, but it's very recommended since it allows you to create new fields 
Area,
CostValue,
Customer,
CustomerClass,
NetSalesValue,
ProductClass,
QtyInvoiced,
Salesperson,
StockCode,
TrnMonth,
TrnYear,
If(Left(ProductClass, 1) = 'A', 'Group A', 'Other') AS ProductGroup // this will create a new field based on the value existing in ProductClass
;
SQL SELECT Area,
CostValue,
Customer,
CustomerClass,
NetSalesValue,
ProductClass,
QtyInvoiced,
Salesperson,
StockCode,
TrnMonth,
TrnYear
FROM SysproCompanyA.dbo.ArTrnDetail;

Hope this helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Using conditions in the script to create groups

kaushiknsolanki — Mon, 20 Jun 2011 15:21:43 GMT

Hi Miguel,

You are right sir, but mrchristopher said that he is not good at sql. And he wanted to group the data.

Thus i have suggested him that way.

Regards,

Kaushik Solanki

Using conditions in the script to create groups

mrchristopher — Mon, 20 Jun 2011 15:53:40 GMT

Both solutions worked well so far, the alternative code has helped me get my head around some of the scripting.

Thank you very much.