Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

mrchristopher
New Contributor

Using conditions in the script to create groups

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.

Tags (1)
1 Solution

Accepted Solutions

Using conditions in the script to create groups

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

5 Replies

Using conditions in the script to create groups

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

mrchristopher
New Contributor

Using conditions in the script to create groups

Excellent soloution, Thank You.

MVP
MVP

Re: Using conditions in the script to create groups

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

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

mrchristopher
New Contributor

Using conditions in the script to create groups

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

Thank you very much.